Data Flow Task Problem: field

Showing posts with label field. Show all posts

Tuesday, March 27, 2012

Data fields in the header

I'm getting this error when I try to preview my report: The value expression
for the textbox 'RepairOrderID' refers to a field. Fields cannot be used in
page headers or footers.
I searched through the news groups and a found suggestion to replace:
Fields!RepairOrderID.Value
with
ReportItems!RepairOrderID.Value
I tried this, it compiles now but when the report renders "#Error" displays
where the RepairOrderID should appear. I found another suggestion to use the
First function this does not work either...
Can someone please help....?
Thanks!!!
DanLooks like your textbox is referring to itself. You need to refer to a
textbox from report body.
More reliable solution is to create read-only parameter with default value
from query and then to use this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> I'm getting this error when I try to preview my report: The value
> expression
> for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> in
> page headers or footers.
> I searched through the news groups and a found suggestion to replace:
> Fields!RepairOrderID.Value
> with
> ReportItems!RepairOrderID.Value
> I tried this, it compiles now but when the report renders "#Error"
> displays
> where the RepairOrderID should appear. I found another suggestion to use
> the
> First function this does not work either...
> Can someone please help....?
> Thanks!!!
> Dan
>|||I don't think my textbox is referring to its self... The name of the textbox
is textbox18, besides I'm explicitly qualifying the Value property with
Fields! or ReportItems!. How do I create a read-only parameter from
query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
Thanks,
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> Looks like your textbox is referring to itself. You need to refer to a
> textbox from report body.
> More reliable solution is to create read-only parameter with default value
> from query and then to use this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> > I'm getting this error when I try to preview my report: The value
> > expression
> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> > in
> > page headers or footers.
> >
> > I searched through the news groups and a found suggestion to replace:
> >
> > Fields!RepairOrderID.Value
> > with
> > ReportItems!RepairOrderID.Value
> >
> > I tried this, it compiles now but when the report renders "#Error"
> > displays
> > where the RepairOrderID should appear. I found another suggestion to use
> > the
> > First function this does not work either...
> >
> > Can someone please help....?
> >
> > Thanks!!!
> >
> > Dan
> >
> >
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
Create a parameter without Prompt , set its default value to From Query,
select your dataset and ReportOrderID as value field.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
>I don't think my textbox is referring to its self... The name of the
>textbox
> is textbox18, besides I'm explicitly qualifying the Value property with
> Fields! or ReportItems!. How do I create a read-only parameter from
> query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> Thanks,
> Dan
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
>> Looks like your textbox is referring to itself. You need to refer to a
>> textbox from report body.
>> More reliable solution is to create read-only parameter with default
>> value
>> from query and then to use this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
>> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
>> > I'm getting this error when I try to preview my report: The value
>> > expression
>> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
>> > used
>> > in
>> > page headers or footers.
>> >
>> > I searched through the news groups and a found suggestion to replace:
>> >
>> > Fields!RepairOrderID.Value
>> > with
>> > ReportItems!RepairOrderID.Value
>> >
>> > I tried this, it compiles now but when the report renders "#Error"
>> > displays
>> > where the RepairOrderID should appear. I found another suggestion to
>> > use
>> > the
>> > First function this does not work either...
>> >
>> > Can someone please help....?
>> >
>> > Thanks!!!
>> >
>> > Dan
>> >
>> >
>>
>|||Excellent!!... That's what I needed... Thanks..:)
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:udrYUvEfEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> Create a parameter without Prompt , set its default value to From Query,
> select your dataset and ReportOrderID as value field.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
> >I don't think my textbox is referring to its self... The name of the
> >textbox
> > is textbox18, besides I'm explicitly qualifying the Value property with
> > Fields! or ReportItems!. How do I create a read-only parameter from
> > query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> >
> > Thanks,
> >
> > Dan
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> >> Looks like your textbox is referring to itself. You need to refer to a
> >> textbox from report body.
> >> More reliable solution is to create read-only parameter with default
> >> value
> >> from query and then to use this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> > wrote
> >> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> >> > I'm getting this error when I try to preview my report: The value
> >> > expression
> >> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
> >> > used
> >> > in
> >> > page headers or footers.
> >> >
> >> > I searched through the news groups and a found suggestion to replace:
> >> >
> >> > Fields!RepairOrderID.Value
> >> > with
> >> > ReportItems!RepairOrderID.Value
> >> >
> >> > I tried this, it compiles now but when the report renders "#Error"
> >> > displays
> >> > where the RepairOrderID should appear. I found another suggestion to
> >> > use
> >> > the
> >> > First function this does not work either...
> >> >
> >> > Can someone please help....?
> >> >
> >> > Thanks!!!
> >> >
> >> > Dan
> >> >
> >> >
> >>
> >>
> >
> >
>

Data Field not Truncating

I have a data field in my report that needs to truncate when it is too long. I do not have "Can Grow" checked, but this field will still run over and interrupt other data.
I cannot reposition this field so it won't interrupt anything else.
Thanks for any help.If the filed has too many characters without a space this may happen. To avoid this create a formula @.MyField having the code
mid(Field,1,20)So only 20 charaters will be printed

Sunday, March 25, 2012

data entry..

sirs,
when i try to enter the data in the every field of table,
i couldn't enter the data and the error message dispaly
with the error "Key column information is insufficient or
incorrect. Too many rows were affected by update." I even
couldn't delete the entire row and get the same error
message. now that specific row has value NULL in every
fields. pls help.What's the structure of your table? Write you SQL commands which you are
entering to INSERT, UPDATE and DELETE the record(s).
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>|||Seems like you forgot to specify a primary key for your table.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"sushil" <anonymous@.discussions.microsoft.com> wrote in message
news:2b0a101c392d5$e458f3b0$a601280a@.phx.gbl...
> sirs,
> when i try to enter the data in the every field of table,
> i couldn't enter the data and the error message dispaly
> with the error "Key column information is insufficient or
> incorrect. Too many rows were affected by update." I even
> couldn't delete the entire row and get the same error
> message. now that specific row has value NULL in every
> fields. pls help.
>sql

Data entry in a datetime field.

My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.

Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1

Here are the contents of the same -

Post#1

Orginally posted by me -

Hi,

I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message -

"Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change."

Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message.

How do I input data into the field?

Post#2

Originally posted by Jens K. Suessmeyer -

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Post#3

Originally posted by me -

Hi,

I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here.

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

(Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?)

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

Post#6

Originally posted by me -

I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software?

Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet.

Post#7

Originally posted by me this afternoon, but it never showed up on the page -

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.

What's the matter? I've created 2 threads for this topic, but it seems no one wants to touch it with a ten-foot pole. Why isn't anyone addressing this issue and giving me a reply on either of my threads?|||

You're probalby not getting a reply because your issue doesn't make sense. There seems to be a terminology issue here... You keep saying that the date value can only be inserted via an Insert statement - well, that's the only way to add new records to a database. An Update statment will work as well if you are updating an existing record.

Date controls are provided that support databinding and the datagrid also supports datetime entries. Perhaps you need to do one of the VB training walkthroughs on data access. Most likely you have a problem with your core data access structure and this datetime issue is just a side effect.

|||

No, it is not a terminology issue. Allow me to demonstrate.

(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).

(I'm using SQL Server 2005 Express SP2 and Management Studio Express)

Observe the data in the database as it stands. The view is in Management Studio Express, and I selected the table in Object Explorer, right clicked it, and selected Open Table. The table opens in a datagrid view in the results pane.

http://img217.imageshack.us/img217/3618/datetime1yf0.jpg

Now, I try to insert a record using the same results pane. Note that everything is entered fine, till I get to the datetime field. (I am using the right arrow key to move to each succesive field, after I have entered data in the previous field). I have entered the data in the datetime field. So far so good.

http://img441.imageshack.us/img441/6154/datetime2fc9.jpg

Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.

http://img227.imageshack.us/img227/377/datetime3sq6.jpg

So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.

In this test design, I have kept the datetime field to allow nulls, so I am bypassing the field altogether, just to demonstrate that I can enter the rest of the record, as well as the rest of the datatypes perfectly well. (My issue relates to both smalldatetime, and datetime types).

http://img441.imageshack.us/img441/408/datetime4ou5.jpg

Now, note, I am going to copy the datetime value from the above record, and paste the exact same value into the current record I am inserting, but I still get the same error. (The above records were inserted using insert statements from the query pane).

http://img441.imageshack.us/img441/6073/datetime5kl5.jpg

I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.

I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.

http://img441.imageshack.us/img441/5144/datetime6ui9.jpg

http://img228.imageshack.us/img228/8219/datetime7vm1.jpg

Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -

http://img408.imageshack.us/img408/3094/datetime8er3.jpg

Same ol' story here as well, so I won't go into the details like above. Just adding a record here as well, getting to the date value, entering it and hitting the right arrow key, and this is what I get, here as well.

http://img228.imageshack.us/img228/4053/datetime9tz7.jpg

Now, I tried databinding the fields to textboxes and datetimepickers in a form once, and then tried running it, and entered a date value. It corrupted my entire .mdf file as well as the log file, and I had to restore the database from a backup. It was a lot of hassle. So I don't fancy attempting doing it again, for the purpose of demonstration. I think you guys get the general idea anyway.

(Please pardon the calculations in the rate x qty= amt, I entered everything in a hurry, and didn't pause to think how many zeroes there were in the quantities. That's why the wierd amounts. I noticed only when it was too late.)

(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).

(I also had to blacken out my machine name for security issues).

I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.

|||That is a better explaination - thank you. However, I can't reproduce the problem. The SQLExpress data base should allow you to enter the value as intended. It sounds like your database and/or development environment are corrupt somehow... Granted, I only tested creating a SQLEx database from within VS (full version), but the table viewer in VS allowed me to enter the date "1-2-2007" without a problem.|||I don't understand how it could get corrupted everytime. I have installed SQL Express non-service pack version, I have at one point used both SP1 and SP2 (each instance was preceeded by a full system/OS reinstall), yet I faced the same issue everytime.|||

You are installing US-EN versions of SQL and VS right?

When you do a fresh install, are you only installing Windows, Visual Studio, SQL Express, and necessary drivers and updates? No extra software like security or other apps? You want to be sure there isn't a conflict. Also, is your software legit (legal copy of windows, vs, etc)? You might even try a different computer (bad harddisk or ram could result in recurring issues) with fresh downloads of the installers for VS and SQL (corrupt installers). Granted, these are unlikely fixes, but I'm out of suggestions.

When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.

|||I am not able to duplicate your issue.

You must have a trigger or a FK or something which is causing the error you are seeing. "Array out of bounds" should not be generated by a datetime field. Even though, I realize, the error is saying it comes from that field, it must be doing something behind the scene causing the error.

Script out your table and post it here.

Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||

Reed,
Yup, both are the US-En versions.

I have 2 totally different computers, both running Win XP Pro though. One is Intel Celeron 800 MHz based, with only 256 MB of memory, and the other has an AMD Opteron 165 with a gig of ram. (Both are home computers, and yes, I did manage to run SQL Server on the older machine satisfactorily, but have given up on it since I got the new machine). I have faced the exact same issue on both systems. So I don't think it could be machine specific. Although, I never tried it on a fresh install of Win XP with only the drivers and SQLExpress and VBExpress. Since these are home machines, I have a ton of other stuff installed on them.
And I reserve the installation of SQL Server and VB for last, since I don't include them in the Norton Ghost image that I make of my installations with all apps installed and settings tweaked.

Tom,
I don't know how to create triggers yet, so there are none in any of my databases. I face this issue in every single (datetime or smalldatetime) field in every single table in every single database attached to SQLExpress, regardless of whether I have specified Foreign Key, or even Primary Key constraints or not.The red exclamation mark just notes that changes to the database (such as updates or inserts) haven't been saved yet.
(I checked the mouse hover tip and it says "This Cell has changed. The change has not been committed to the database. The original data is NULL." NOthing to worry about. It does that to all fields that are set to allow nulls, I think.

Something about what Reed said set me thinking. I tweak almost every OS setting to my exacting requirements whenever I first install it. I have tweaked the datetime settings of Windows. I also disable unnecessary services. Does that have anything to do with the way SQL Server is behaving? What windows services should be running for SQL Server to run without hiccups? Is there any incompatibility with tweaked windows datetime settings?

Here's the scripted table as requested. It's the same as the one I've used in my example pics. I have removed the database name wherever it appears for security and privacy concerns, so don't be alarmed or think that the issue is because I don't have the dbname specified where it ought to be. ;-)
(Note, I added the check constraints and the last column after I posted the pics).

Code Snippet

USE [.MDF]
GO
/****** Object: Table [dbo].[Purchases] Script Date: 06/14/2007 17:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Purchases](
[PurchaseNo] [bigint] NOT NULL,
[BillNo] [varchar](50) NULL,
[Date] [datetime] NULL,
[Broker] [varchar](50) NULL,
[ShareName] [varchar](50) NULL,
[Quantity] [int] NULL,
[Rate] [numeric](12, 2) NULL,
[Amount] [numeric](12, 2) NULL,
[Old] [char](1) NULL,
[Balance] [char](1) NULL,
CONSTRAINT [PK_Purchases] PRIMARY KEY CLUSTERED
(
[PurchaseNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR

[Balance]='f'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Balancecheck]
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Oldcheck] CHECK (([Old]='n' OR [Old]='y'))
GO
ALTER TABLE [dbo].[Purchases] CHECK CONSTRAINT [Oldcheck]

|||

Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.

If you know precisely what changes you've made to the default date/time settings you should undo them one at a time and test the app until it works. That would at least let you understand which tweak caused the issue. Its possible that the change you made broke the table editors in question and that the failure is expected. It is also possible that this is indeed a bug in the software due to an oversight in properly checking the

System.Globalization.DateTimeFormatInfo

before trying to parse a date.

See where undoing your tweaks gets you. If you have the resources, it might be even better to start with a clean, fresh install of windows and then apply your tweaks one at a time until the program breaks.

|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).

What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?

I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.

|||

This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.

http://img233.imageshack.us/img233/8202/date1wi1.jpg

http://img233.imageshack.us/img233/2007/time1li4.jpg

Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.

1-1-2007
01-01-2007
01/01/2007
01,01,2007
01:01:2007
Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)
I even tried entering time alongside with all those above figures in the format -
11:35 PM
11:35 AM
23:35

I have tried entering these very combinations through both insert statements, and the GUIs. Only one works. So it doesn't really matter if I've changed the way dates are displayed in Windows, does it? The GUI should still accept the values, especially if the code is accepting the same values.

|||

reachrishikh wrote:

...

Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.

...The GUI should still accept the values, especially if the code is accepting the same values.

I don't think this is the case... SQL has it's own formats that it expects values to be entered in. I don't think it takes its formats from Windows. However, text input boxes, such as those found in a data grid of some kind, would most likely be affected by Windows format settings. I might be wrong here, but this is what makes me think it is a settings problem and not an issue with SQL.

There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).

|||

So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?

Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.

|||

Orignal 2 digit year was 2029

Short date format was M/d/yyyy

Date separator was /

Data entry in a datetime field.

My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.

Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1

Here are the contents of the same -

Post#1

Orginally posted by me -

Hi,

How do I input data into the field?

Post#2

Originally posted by Jens K. Suessmeyer -

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Post#3

Originally posted by me -

Hi,

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

Post#6

Originally posted by me -

Post#7

Originally posted by me this afternoon, but it never showed up on the page -

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.

|||

No, it is not a terminology issue. Allow me to demonstrate.

(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).

(I'm using SQL Server 2005 Express SP2 and Management Studio Express)

http://img217.imageshack.us/img217/3618/datetime1yf0.jpg

http://img441.imageshack.us/img441/6154/datetime2fc9.jpg

Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.

http://img227.imageshack.us/img227/377/datetime3sq6.jpg

So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.

http://img441.imageshack.us/img441/408/datetime4ou5.jpg

http://img441.imageshack.us/img441/6073/datetime5kl5.jpg

I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.

I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.

http://img441.imageshack.us/img441/5144/datetime6ui9.jpg

http://img228.imageshack.us/img228/8219/datetime7vm1.jpg

Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -

http://img408.imageshack.us/img408/3094/datetime8er3.jpg

http://img228.imageshack.us/img228/4053/datetime9tz7.jpg

(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).

(I also had to blacken out my machine name for security issues).

I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.

You are installing US-EN versions of SQL and VS right?

When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.

|||I am not able to duplicate your issue.

Script out your table and post it here.

Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||

Reed,
Yup, both are the US-En versions.

Code Snippet

ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR

|||

Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.

System.Globalization.DateTimeFormatInfo

before trying to parse a date.

|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).

What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?

I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.

|||

This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.

http://img233.imageshack.us/img233/8202/date1wi1.jpg

http://img233.imageshack.us/img233/2007/time1li4.jpg

|||

reachrishikh wrote:

...

Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.

...The GUI should still accept the values, especially if the code is accepting the same values.

There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).

|||

So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?

Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.

|||

Orignal 2 digit year was 2029

Short date format was M/d/yyyy

Date separator was /

Data entry in a datetime field.

My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead.

Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1

Here are the contents of the same -

Post#1

Orginally posted by me -

Hi,

How do I input data into the field?

Post#2

Originally posted by Jens K. Suessmeyer -

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Post#3

Originally posted by me -

Hi,

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

Post#6

Originally posted by me -

Post#7

Originally posted by me this afternoon, but it never showed up on the page -

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.

|||

No, it is not a terminology issue. Allow me to demonstrate.

(God bless ImageShack, I wasn't aware of it when I posted this issue in my original thread).

(I'm using SQL Server 2005 Express SP2 and Management Studio Express)

http://img217.imageshack.us/img217/3618/datetime1yf0.jpg

http://img441.imageshack.us/img441/6154/datetime2fc9.jpg

Now note, the second I click the left arrow key, to indicate that I have finished entering data in the field, and that I need to go to another field, I get the following error message.

http://img227.imageshack.us/img227/377/datetime3sq6.jpg

So I have to hit escape twice to get rid of all the data I tried to insert, and we get back to the image displayed in the first link I posted above.

http://img441.imageshack.us/img441/408/datetime4ou5.jpg

http://img441.imageshack.us/img441/6073/datetime5kl5.jpg

I tried to paste the copied datetime value from the above record into the record below, where I had left it null. Get the error again.

I tried to paste the copied value in a new record I created below, leaving the null value in the above record intact, and I still get the same 'ol error message.

http://img441.imageshack.us/img441/5144/datetime6ui9.jpg

http://img228.imageshack.us/img228/8219/datetime7vm1.jpg

Now, on to VBExpress. We open a project first. Now we go to the database explorer pane, go to our database, select the table in question, right click and select 'show table data' to get this tab -

http://img408.imageshack.us/img408/3094/datetime8er3.jpg

http://img228.imageshack.us/img228/4053/datetime9tz7.jpg

(I haven't entered any records between purchase no 1 and 21, values are entered arbitrarily. It is the PK field).

(I also had to blacken out my machine name for security issues).

I hope now people have a better idea of what this issue is.I have elaborated the issue quite lucidly. Hopefully, it is a bug, and will be fixed my MS soon.

You are installing US-EN versions of SQL and VS right?

When no one else reports the same issue, and we can't reproduce it, it almost always means its something specific to your computer.

|||I am not able to duplicate your issue.

Script out your table and post it here.

Also, I see a red ! on your images from the BILLID field. What does the ! say? That might actually be the problem.
|||

Reed,
Yup, both are the US-En versions.

Code Snippet

ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Purchases] WITH CHECK ADD CONSTRAINT [Balancecheck] CHECK (([Balance]='e' OR [Balance]='p' OR

|||

Ok, it may very well be a result of "tweaking" the windows datetime settings. The services thing wouldn't cause it - if you disabled a service that SQL needed, it wouldn't run.

System.Globalization.DateTimeFormatInfo

before trying to parse a date.

|||I am unable to duplicate your problem using the code you posted on SQL 2005 SP1 (not SQLExpress but should be the same).

What do you mean by "tweaked datetime settings"? What exactly did you change and how? And I guess more importantly why?

I THINK the Mgt Console uses the Windows date format to know what to expect. If you changed the format, that is very likely your issue.

|||

This is what has been tweaked. The values are not the default ones that windows supplies when doing a fresh install. I don't recall what the default values are.

http://img233.imageshack.us/img233/8202/date1wi1.jpg

http://img233.imageshack.us/img233/2007/time1li4.jpg

|||

reachrishikh wrote:

...
Also, I don't think changing the date time settings could be an issue, because, as noted in my very first post above, I have tried entering all these combinations of values using insert statements, and they all worked fine.
...The GUI should still accept the values, especially if the code is accepting the same values.

There's still the possibility of some other software conflict or corruption since you haven't tried a clean install (using a disk image doesn't count as a "clean" install).

|||

So could any of you please supply me with the default windows date and time settings, so I can (for the time being) revert to the original settings and see if it works?

Also, I haven't got the time this week, so I'll set aside a day next week, and try doing the clean install as you guys suggested, and see if I still get the error even then.

|||

Orignal 2 digit year was 2029

Short date format was M/d/yyyy

Date separator was /

Data entry in a datetime field

Hi,

"Invalid Value for cell (row 1, column 2).

The changed value in this cell was not recognised as valid.

.Net Framework Datatype: Datetime

Error Message: Index was outside the bounds of the array.

Type a value appropriate for this data type or press ESC to cancel the change."

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||Guys, I need your help. Please give me a solution to my problem. (This post has already been relegated to the second page without any replies).|||People, I need some help here. Any replies addressing my issue will be greatly appreciated. Thank You in advance.|||

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

sql

Data entry in a datetime field

Hi,

How do I input data into the field?

Hi,

you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) -

1-1-2007

01-01-2007

01/01/2007

01,01,2007

01:01:2007

Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy)

I even tried entering time alongside with all those above figures in the format -

11:35 PM

11:35 AM

23:35

The only time value(s) I didn't try was/were - hh:mm:ss tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values.

I also tried entering a time value standalone, without the date. Still no go.

Now I'm in a real fix because of the inability to enter date/time values.

Could you please help me out?

|||

Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2.

I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem?

Thursday, March 22, 2012

Data Driven Subscription Help

Hello all,

I am trying to schedule an MDX report to run once a week and it only works if I pass one parameter per field.It would fail every time I try to pass multiple parameters.Do you know if there is way to pass multiple MDX parameters?

The parameter field is set as multi-value field.

This worked –

'[Dim Travel Product].[Dim Travel Product].&[67]'

This failed –

'[Dim Travel Product].[Dim Travel Product].&[67], [Dim Travel Product].[Dim Travel Product].&[70], [Dim Travel Product].[Dim Travel Product].&[69]'

This failed -

'[Dim Travel Product].[Dim Travel Product].&[67]&[69]&[70]'

Could you post the MDX statement into which the parameters are being passed?

Thanks,
Bryan

|||

The parameter is passed to @.TravelProduct:

SET [Dim Travel Product Set] AS StrToSet(@.TravelProduct)

MEMBER [Dim Travel Product].[Dim Travel Product].[Travel Product Subset] AS 'Aggregate([Dim Travel Product Set])'

Thanks in advance!

|||It also works if i were to run this manually and select multiple parameters. The problem is I don't know the proper syntax of what's being passed when there are multiple selections.

|||

Enclose the list of members inside { } when passing multiple members.

|||

None of the below syntax works:

'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}'

'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}'

This is the method that I am using to pass the parameter:

So this is still working:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'[Dim Travel Product].[Dim Travel Product].&[4]' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

|||

Take a look at this. It works against Adventure Works:

Code Snippet

select

[Date].[Date].[July 1, 2003] on 0,

strtoset("{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}") on 1

from [Adventure Works]

B.|||

What I am trying to do is pass the @.TravelProduct from a Data Driven Subscription in Reporting Services and schedule it to run once a week. The select statement that I built contains all my filters, but when I add more than one filter for a particular field, it errors.

Thanks!

|||

What my code illustrates is how the MDX must be constructed to support multiple values from a parameter. You need to use STRTOSET, you need to wrap that comma delimited list of set members in curly braces, and that set string needs to be wrapped in double-quotes. If you've got all that in place, you can use a parameter in SSRS supply the set of members. The trick is getting your MDX in order to be able to work with the value from the parameter.

|||

Thanks! I'll try that later today and report back.

|||

I tried what Bryan did above but it is still not working. ;(

sql

Monday, March 19, 2012

data conversion in during DTS import

Hi,
I have a text file in which one field is a date but in
file it is in '20030818' format and I am tring to import
that file to a table which has a column datetime.
is it possible to convert '20030818' to datetime during
DTS import?
if yes please let me know how ?
Thanks.use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>

Sunday, March 11, 2012

data conversion - numeric to string loosing precision

Hi All,

i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.

the problem is when this numeric field is > 0 it looses the precision on its decimal value.

example, if numeric value is 0.32

after converting this to a string, the new value will be : .32

it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.

when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.

can any one help on this?

Is your ultimate database target for the numeric data type a character based column, hence the need to retain the leading zeros?

If that is the case, retaining leading zeros can be accomplished with a derived column transform, rather than a data conversion transform. There you have access to the SSIS expression language, with a relatively standard, if meager, set of string functions, as well as type casts.

Another alternative for pretty much an data type conversion is a Script Transform, which will afford you the full power of the .NET framework, including in this case custom numeric formats for types destinated for strings.|||

Hi jaegd,

i tried that, but no luck. i use a derived table and this is my expression

ISNULL(investmentPercentage) ? 0.00 : investmentPercentage

here investmentPercentage is a numeric (15,2). i add the above expression as a new column (string) (8 chars). but still it looses the 0

.00 when it's null

.32 when it;s 0.32

any comments

|||

Use the Script Task, and follow the instructions in this link for numeric format specifiers for strings. You need to do something similar to this...

Dim number as float

number.ToString("D")

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp

|||

In the derived column, you can still acheive it with conditional logic The script as mentioned above is cleaner, but if you want to avoid adding that, you can try an expression like:

(investmentPercentage < 1 ? "0" : "") + (DT_WSTR, 20)investmentPercentage

This expression will add a "0" to the beginning of the string if the number is less than one (which is the only time the zero is dropped). When the number is greater than or equal to 1, an empty string is added.

NOTE: this does not take into account negative values. If investmentPercentage can be negative, you will need to tweak the expression a little (or post here, and I can help with that).

Thanks
Mark

|||

Hi Mark,

thanks for that, but it sort of half answer my question.

yes investmentPercentage can be negative or can even be null. i was wondering if there's an eaisier way. i've got a lot of cloumns like this, writing an expression for each of them this long can be tedious. is this how you would "tweak" it?

ISNULL(investmentPercentage) ? "0.00" : (investmentPercentage < 1 && investmentPercentage >-1 ? "0" : "") + (DT_WSTR, 8)investmentPercentage

|||

You might find it easier to use string formatting in the script component... I can't think of any other way that would be better.

As for tweaking the expression, you will have to do some extra things to take care of the negative sign (so the prepended zero doesn't end up before the negative sign). The following expression adds a case for values between -1 and 0 to handle that:

ISNULL(investmentPercentage) ? (DT_WSTR,8)"0.00" : investmentPercentage < 0 && investmentPercentage > -1 ? "-0" + SUBSTRING((DT_WSTR,8)investmentPercentage, 2, 8) : ((investmentPercentage >= 0 && investmentPercentage < 1 ? "0" : "") + (DT_WSTR,8)investmentPercentage

Let me know if this does the trick for you.

Thanks
Mark

Data Conversion

Hello everyone,

I am running SQL-2000, I have a table that one field ddefined as char. The data is actually Dollar values(no $ signs just 99.25 for example). I need to convert this column from char to Numeric. I am trying to use Enterprise manager to redesign the table but I get "error converting data type VARCHAR to numeric". Enterprise manager shows the field as CHAR. I have no Idea why that error is comming up. I would like any info that could help me with this conversion. Thanks in advance.

EvThe isnum function can be used to find values in your data which cannot be converted to numbers:

select *
from [YOURTABLE]
where isnum([VALUEFIELD]) = 0|||Hi you can use the following query
select value=convert(numeric,ddefined) from table

Madhivanan|||I ran the ISNUM function and all rows are good. However I get the same error.|||I might be off here, but perhaps there's a value in there that's a valid numeric but can't be converted from varchar to numeric (see: http://www.dbforums.com/t998353.html) ? Anyway, I ran into the same thing a while back; http://www.dbforums.com/t1023776.html got it solved.|||You are right. I am not sure why I can't convert to Numeric but I am able to convert to MONEY. Thats works fine for the application. Thanks for your help.

Ev

Data Conversion

Hello,

What will the Dataconversion do if I try to convert a source field to a datetime but the data is not a valid date?

Can I just skip the one field?

Thanks,

Michael

Hi Michael,

Most likely, you will get a conversion error. If you open the UI for the data conversion, and hit the "Configure Error Output" button, you can set the error handling behaviour on a column-by-column basis. If you just want to skip that field, you could set the error behaviour for that column to "Ignore Failure". That column will be set to NULL.

Let me know if you have further questions about this.

Thanks
Mark

Thursday, March 8, 2012

data cleaning

Hey gang, I have 2 100,000 record tables, with a ID & address field. (I've exclude all the other field for now) I need to make sure the ID field in table A is equal to table B and that the address in table A is equal to table B. If the Id's or address do not match create a separate table. Any good ideascreate table table3
( ID integer
, address varchar(255)
)

insert into table3
select ID
, address
from table1
where not exists
( select * from table2
where ID = table1.ID )
union all
select ID
, address
from table2
where not exists
( select * from table1
where ID = table2.ID )
union all
select table1.ID
, table1.address
from table1
inner
join table2
on table1.ID = table2.ID
where table1.address <> table2.address
union all
select table2.ID
, table2.address
from table1
inner
join table2
on table1.ID = table2.ID
where table1.address <> table2.address|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Table1([ID] int, address varchar(255))
CREATE TABLE Table2([ID] int, address varchar(255))
GO

INSERT INTO Table1([ID],address)
SELECT 1, 'Here' UNION ALL
SELECT 2, 'There' UNION ALL
SELECT 3, 'And Everywhere'

INSERT INTO Table2([ID],address)
SELECT 0, 'Here' UNION ALL
SELECT 2, 'There' UNION ALL
SELECT 3, 'And Everywheres'
GO

SELECT [ID], address
INTO Table3
FROM (
SELECT [ID]
, address
FROM table1
UNION ALL
SELECT [ID]
, address
FROM table2) AS XXX
GROUP BY [ID], address
HAVING COUNT(*) = 1
GO

SELECT * FROM Table3
GO

SET NOCOUNT OFF
DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Table3
GO

Tuesday, February 14, 2012

Cutomizing Exported Excel tabs

Hi All
I have a very simple report that is grouped on a particular field. I have
also set the "Page Break at end property" set to true for this group. I wish
to set up a subscription that will email this report as an excel attachment.
The excel export shows the records of each group in a separate sheet -
Exactly the way I need. What I also want is that the tabs of each sheet
should show the group field name as its name instead of sheet1, sheet2
etc....
Is there any way to achieve this ?
ThanksSorry, there is no property in the RDL that would allow you to achieve
customizing the sheet names.
It is under consideration for inclusion in a future release.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"PR" <PR@.discussions.microsoft.com> wrote in message
news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
> Hi All
> I have a very simple report that is grouped on a particular field. I have
> also set the "Page Break at end property" set to true for this group. I
wish
> to set up a subscription that will email this report as an excel
attachment.
> The excel export shows the records of each group in a separate sheet -
> Exactly the way I need. What I also want is that the tabs of each sheet
> should show the group field name as its name instead of sheet1, sheet2
> etc....
> Is there any way to achieve this ?
> Thanks|||Robert
Can it be moved off the consideration list onto the must do list?
Providing the functionality to output to multiple sheets without the ability
to dynamically name those sheets makes multiple sheets pretty pointless.
I have one report that generates an Excel file for state managers. The file
contains a seperate sheet for each consultant in their state. As I can't
name the sheets from RS all they see is Sheet1, Sheet2, etc...
One of these files has over 200 sheets and the generic naming is a major
pain in the butt. Due to this problem most of the managers won't sign-off on
the costs for developing/deploying RS at our site.
Thanks
Phill
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Sorry, there is no property in the RDL that would allow you to achieve
> customizing the sheet names.
> It is under consideration for inclusion in a future release.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "PR" <PR@.discussions.microsoft.com> wrote in message
> news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
>> Hi All
>> I have a very simple report that is grouped on a particular field. I have
>> also set the "Page Break at end property" set to true for this group. I
> wish
>> to set up a subscription that will email this report as an excel
> attachment.
>> The excel export shows the records of each group in a separate sheet -
>> Exactly the way I need. What I also want is that the tabs of each sheet
>> should show the group field name as its name instead of sheet1, sheet2
>> etc....
>> Is there any way to achieve this ?
>> Thanks
>
>|||I use the document map facility to get aroung this. It exports as the front
sheet of the excel file and clicking on each title takes you to the correct
sheet.
"news.microsoft.com" <pcarter_NOT_AT_SPAM_bellpotter.com.au> wrote in
message news:ueUeHQPBFHA.2624@.TK2MSFTNGP11.phx.gbl...
> Robert
> Can it be moved off the consideration list onto the must do list?
> Providing the functionality to output to multiple sheets without the
> ability to dynamically name those sheets makes multiple sheets pretty
> pointless.
> I have one report that generates an Excel file for state managers. The
> file contains a seperate sheet for each consultant in their state. As I
> can't name the sheets from RS all they see is Sheet1, Sheet2, etc...
> One of these files has over 200 sheets and the generic naming is a major
> pain in the butt. Due to this problem most of the managers won't sign-off
> on the costs for developing/deploying RS at our site.
> Thanks
> Phill
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>> Sorry, there is no property in the RDL that would allow you to achieve
>> customizing the sheet names.
>> It is under consideration for inclusion in a future release.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "PR" <PR@.discussions.microsoft.com> wrote in message
>> news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
>> Hi All
>> I have a very simple report that is grouped on a particular field. I
>> have
>> also set the "Page Break at end property" set to true for this group. I
>> wish
>> to set up a subscription that will email this report as an excel
>> attachment.
>> The excel export shows the records of each group in a separate sheet -
>> Exactly the way I need. What I also want is that the tabs of each sheet
>> should show the group field name as its name instead of sheet1, sheet2
>> etc....
>> Is there any way to achieve this ?
>> Thanks
>>
>|||I agree with you Phill... my company likes seamless transitions. Reporting
Services would've been the easiest solution if we had control on the
sheetnames.
I'm keeping my fingers crossed that this is included in the SP2 that's going
to be realeased soon (mid-April last I read). I cannot see the funcitonal
enhancements list because it is in the beta site which I don't have access
to.
Pete
"news.microsoft.com" wrote:
> Robert
> Can it be moved off the consideration list onto the must do list?
> Providing the functionality to output to multiple sheets without the ability
> to dynamically name those sheets makes multiple sheets pretty pointless.
> I have one report that generates an Excel file for state managers. The file
> contains a seperate sheet for each consultant in their state. As I can't
> name the sheets from RS all they see is Sheet1, Sheet2, etc...
> One of these files has over 200 sheets and the generic naming is a major
> pain in the butt. Due to this problem most of the managers won't sign-off on
> the costs for developing/deploying RS at our site.
> Thanks
> Phill
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OA99z8MBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> > Sorry, there is no property in the RDL that would allow you to achieve
> > customizing the sheet names.
> > It is under consideration for inclusion in a future release.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "PR" <PR@.discussions.microsoft.com> wrote in message
> > news:FEDD440A-49E9-4F2F-9DA3-08CC995E2D7B@.microsoft.com...
> >> Hi All
> >>
> >> I have a very simple report that is grouped on a particular field. I have
> >> also set the "Page Break at end property" set to true for this group. I
> > wish
> >> to set up a subscription that will email this report as an excel
> > attachment.
> >> The excel export shows the records of each group in a separate sheet -
> >> Exactly the way I need. What I also want is that the tabs of each sheet
> >> should show the group field name as its name instead of sheet1, sheet2
> >> etc....
> >>
> >> Is there any way to achieve this ?
> >>
> >> Thanks
> >
> >
> >
>
>

Tuesday, March 27, 2012

Data fields in the header

Data Field not Truncating

Sunday, March 25, 2012

data entry..

Data entry in a datetime field.

Data entry in a datetime field.

Data entry in a datetime field.

Data entry in a datetime field

Data entry in a datetime field

Thursday, March 22, 2012

Data Driven Subscription Help

Monday, March 19, 2012

data conversion in during DTS import

Sunday, March 11, 2012

data conversion - numeric to string loosing precision

Data Conversion

Data Conversion

Thursday, March 8, 2012

data cleaning

Tuesday, February 14, 2012

Cutomizing Exported Excel tabs

Data Flow Task Problem

Blog Archive

About Me