Hi,
I have a stored procedure which has a cursor that populates a table
datatype variabel with few fields on eof which is of type [nText].
Now for each row in the above table type variabel I have to call
another stored proc to which I have to pass the [nText] field value.
Can anyone suggest how to do it?
Thanks,
Vikas.Could you call the stored procedure passing the ntext value before inserting
it into the table variable? You could call it after inserting as well, but
before fetching the next value from the cursor.
"vikassah@.gmail.com" wrote:
> Hi,
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
> Thanks,
> Vikas.
>|||(vikassah@.gmail.com) writes:
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
I don't really understand what you are trying to do. Maybe you could
be more specific?
Note, though that you cannot assign to variables of the type ntext,
so you may have to rework your strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
excluding fields in SELECT statement
Hi,
Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.
Thanks"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks
No. It isn't difficult to list just the columns you want. In Query Analyzer
you can drag the column list from the Object Browser pane so no typing is
required.
Note that it is also good practice to avoid using SELECT * in production
code. Using * instead of listing just the required columns is not only
inefficient, it also impacts the reliability and maintainability of your
code.
--
David Portas
SQL Server MVP
--|||No, there is no Transact-SQL syntax for this. However, you can use Query
Analyzer to generate a SELECT statement for the desired view or table and
then remove the unneeded columns.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks|||>> Is there a way to exclude fields [sic] in a query other than just including the ones you want. If there are 20 fields [sic] and you want to see all but 3, it would be a lot easier to exclude the 3. <<
Short answer: No. Have you ever seen such a thing in any other
programming language? The closest thing I know is FILLER in Cobol.
Rows are not records; fields are not columns; tables are not files. It
is not easier; it is dangerous. If the base table is altered, your
syntax would not see the changes and would screw up. A list of column
name is easy to generate from the schema information tables with a
tool.|||Great!
Forgot to mention I was looking for a faster way to work in the Query
Analyzer.
>>you can use Query Analyzer to generate a SELECT statement for the desired view...
Is this something other than dragging over columns into the SELECT
statement you're building?
Thanks,
Ric|||ric (rtavert@.yahoo.com) writes:
>>>you can use Query Analyzer to generate a SELECT statement for the desired
>>>view...
> Is this something other than dragging over columns into the SELECT
> statement you're building?
I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.
There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I guess this is what Dan had in mind.
I used to use that method in the old days (pre- SQL 2000) but I was
referring to the following technique:
1) right-click on the desired table in the QA object browser
2) select Script Object to New Window as--> Select
3) remove unwanted columns
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F9F4031F9D6Yazorman@.127.0.0.1...
> ric (rtavert@.yahoo.com) writes:
>>>>you can use Query Analyzer to generate a SELECT statement for the
>>>>desired
>>>>view...
>>
>> Is this something other than dragging over columns into the SELECT
>> statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Expanding '*' is something people have asked for, and its on the list,
but not there yet -
http://www.promptsql.com/known_bugs...nhancements.htm
Regards,
Damian (PromptSQL developer)
Erland Sommarskog wrote:
> ric (rtavert@.yahoo.com) writes:
> >>>you can use Query Analyzer to generate a SELECT statement for the desired
> >>>view...
> > Is this something other than dragging over columns into the SELECT
> > statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I said expanding '*' was on the list of things to be implemented --
we've just released PromptSQL 1.2 Beta one, and this was one of the new
features.
You can now type
SELECT *[TAB] FROM Orders, Customers
and the * will be expanded to insert all the columns in Orders and
Customers, prefixed by appropriate aliases. The separator is
customizable.
Or you can type:
SELECT o.*[TAB] FROM Orders o, Customers and the o.* will be replaced
by a list of all Orders columns, prefixed by "o."
Regards,
Damian (PromptSQL developer)
http://www.promptsql.com
Exclude rows with no data
I have a report created using MDX imported from another application. In
order to provide flexibility, this MDX carries in several fields that are not
necessarily used when the report is created. (The report prompts for
parameters which determine whether it should be created using budget or
actual data.) Because of this, I am getting rows of data where there are no
values for the type of report I am requesting. For example, if I have budget
data but no actual data for an item, this item still shows up when I request
the report for actual data. Is there any way to prevent these rows from
appearing?On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have a report created using MDX imported from another application. In
> order to provide flexibility, this MDX carries in several fields that are not
> necessarily used when the report is created. (The report prompts for
> parameters which determine whether it should be created using budget or
> actual data.) Because of this, I am getting rows of data where there are no
> values for the type of report I am requesting. For example, if I have budget
> data but no actual data for an item, this item still shows up when I request
> the report for actual data. Is there any way to prevent these rows from
> appearing?
I'm not familiar w/MDX; however, you should be able to filter the
results by something like Sum(Fields!ActualData.Value) > 0. Hope this
is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
message that says "Processing of filter can not be performed. Please check
the data type value retuend by the filter expression." Can you tell me what
this means and how to set up the statement properly. Thank you for your
help. I'm learning this tool on a "need to know" basis and I have 60 reports
to create w/o any real training.
"EMartinez" wrote:
> On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I have a report created using MDX imported from another application. In
> > order to provide flexibility, this MDX carries in several fields that are not
> > necessarily used when the report is created. (The report prompts for
> > parameters which determine whether it should be created using budget or
> > actual data.) Because of this, I am getting rows of data where there are no
> > values for the type of report I am requesting. For example, if I have budget
> > data but no actual data for an item, this item still shows up when I request
> > the report for actual data. Is there any way to prevent these rows from
> > appearing?
> I'm not familiar w/MDX; however, you should be able to filter the
> results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> message that says "Processing of filter can not be performed. Please check
> the data type value retuend by the filter expression." Can you tell me what
> this means and how to set up the statement properly. Thank you for your
> help. I'm learning this tool on a "need to know" basis and I have 60 reports
> to create w/o any real training.
> "EMartinez" wrote:
> > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I have a report created using MDX imported from another application. In
> > > order to provide flexibility, this MDX carries in several fields that are not
> > > necessarily used when the report is created. (The report prompts for
> > > parameters which determine whether it should be created using budget or
> > > actual data.) Because of this, I am getting rows of data where there are no
> > > values for the type of report I am requesting. For example, if I have budget
> > > data but no actual data for an item, this item still shows up when I request
> > > the report for actual data. Is there any way to prevent these rows from
> > > appearing?
> > I'm not familiar w/MDX; however, you should be able to filter the
> > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > is helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
It seems like you might not have set the 'Value' part of the filter to
an integer value. By default, the filter sets all numbers in the
'Value' column to strings unless you use an = sign before it. You
might want to try something like: in the 'Expression' column enter:
=Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
the 'Value' column enter: =0
If this does not correct the problem, you will most likely need to
convert the results of 'Fields!GrossSales.Value' to an integer: via
something like: CInt(Fields!GrossSales.Value).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. I had to use the "cint" in the expression column AND the "=0"
in the value column. I appreciate youe help!
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||One more question....For some reason the Table filter list has the and/or
column on the selection grayed out. I would like to be able to say "sales OR
pounds" not =0. Do you have any idea why the and/or column is not available
under the table filter properties?
Thank you,
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 5, 7:16 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> One more question....For some reason the Table filter list has the and/or
> column on the selection grayed out. I would like to be able to say "sales OR
> pounds" not =0. Do you have any idea why the and/or column is not available
> under the table filter properties?
> Thank you,
> PB
> "EMartinez" wrote:
> > On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > > message that says "Processing of filter can not be performed. Please check
> > > the data type value retuend by the filter expression." Can you tell me what
> > > this means and how to set up the statement properly. Thank you for your
> > > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > > to create w/o any real training.
> > > "EMartinez" wrote:
> > > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > > I have a report created using MDX imported from another application. In
> > > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > > necessarily used when the report is created. (The report prompts for
> > > > > parameters which determine whether it should be created using budget or
> > > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > > values for the type of report I am requesting. For example, if I have budget
> > > > > data but no actual data for an item, this item still shows up when I request
> > > > > the report for actual data. Is there any way to prevent these rows from
> > > > > appearing?
> > > > I'm not familiar w/MDX; however, you should be able to filter the
> > > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > > is helpful.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. SQL Server Developer
> > It seems like you might not have set the 'Value' part of the filter to
> > an integer value. By default, the filter sets all numbers in the
> > 'Value' column to strings unless you use an = sign before it. You
> > might want to try something like: in the 'Expression' column enter:
> > =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> > the 'Value' column enter: =0
> > If this does not correct the problem, you will most likely need to
> > convert the results of 'Fields!GrossSales.Value' to an integer: via
> > something like: CInt(Fields!GrossSales.Value).
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad to be of assistance. I have not been able to get
the filter options to allow 'or' in my experiences so far. You might
need to have the filtering in the query or stored procedure that is
sourcing the report to allow for more flexibility. Just a thought.
Regards,
Enrique Martinez
Sr. SQL Server Developer
order to provide flexibility, this MDX carries in several fields that are not
necessarily used when the report is created. (The report prompts for
parameters which determine whether it should be created using budget or
actual data.) Because of this, I am getting rows of data where there are no
values for the type of report I am requesting. For example, if I have budget
data but no actual data for an item, this item still shows up when I request
the report for actual data. Is there any way to prevent these rows from
appearing?On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have a report created using MDX imported from another application. In
> order to provide flexibility, this MDX carries in several fields that are not
> necessarily used when the report is created. (The report prompts for
> parameters which determine whether it should be created using budget or
> actual data.) Because of this, I am getting rows of data where there are no
> values for the type of report I am requesting. For example, if I have budget
> data but no actual data for an item, this item still shows up when I request
> the report for actual data. Is there any way to prevent these rows from
> appearing?
I'm not familiar w/MDX; however, you should be able to filter the
results by something like Sum(Fields!ActualData.Value) > 0. Hope this
is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
message that says "Processing of filter can not be performed. Please check
the data type value retuend by the filter expression." Can you tell me what
this means and how to set up the statement properly. Thank you for your
help. I'm learning this tool on a "need to know" basis and I have 60 reports
to create w/o any real training.
"EMartinez" wrote:
> On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I have a report created using MDX imported from another application. In
> > order to provide flexibility, this MDX carries in several fields that are not
> > necessarily used when the report is created. (The report prompts for
> > parameters which determine whether it should be created using budget or
> > actual data.) Because of this, I am getting rows of data where there are no
> > values for the type of report I am requesting. For example, if I have budget
> > data but no actual data for an item, this item still shows up when I request
> > the report for actual data. Is there any way to prevent these rows from
> > appearing?
> I'm not familiar w/MDX; however, you should be able to filter the
> results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> message that says "Processing of filter can not be performed. Please check
> the data type value retuend by the filter expression." Can you tell me what
> this means and how to set up the statement properly. Thank you for your
> help. I'm learning this tool on a "need to know" basis and I have 60 reports
> to create w/o any real training.
> "EMartinez" wrote:
> > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I have a report created using MDX imported from another application. In
> > > order to provide flexibility, this MDX carries in several fields that are not
> > > necessarily used when the report is created. (The report prompts for
> > > parameters which determine whether it should be created using budget or
> > > actual data.) Because of this, I am getting rows of data where there are no
> > > values for the type of report I am requesting. For example, if I have budget
> > > data but no actual data for an item, this item still shows up when I request
> > > the report for actual data. Is there any way to prevent these rows from
> > > appearing?
> > I'm not familiar w/MDX; however, you should be able to filter the
> > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > is helpful.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
It seems like you might not have set the 'Value' part of the filter to
an integer value. By default, the filter sets all numbers in the
'Value' column to strings unless you use an = sign before it. You
might want to try something like: in the 'Expression' column enter:
=Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
the 'Value' column enter: =0
If this does not correct the problem, you will most likely need to
convert the results of 'Fields!GrossSales.Value' to an integer: via
something like: CInt(Fields!GrossSales.Value).
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thank you. I had to use the "cint" in the expression column AND the "=0"
in the value column. I appreciate youe help!
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||One more question....For some reason the Table filter list has the and/or
column on the selection grayed out. I would like to be able to say "sales OR
pounds" not =0. Do you have any idea why the and/or column is not available
under the table filter properties?
Thank you,
PB
"EMartinez" wrote:
> On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > message that says "Processing of filter can not be performed. Please check
> > the data type value retuend by the filter expression." Can you tell me what
> > this means and how to set up the statement properly. Thank you for your
> > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > to create w/o any real training.
> >
> > "EMartinez" wrote:
> > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > I have a report created using MDX imported from another application. In
> > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > necessarily used when the report is created. (The report prompts for
> > > > parameters which determine whether it should be created using budget or
> > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > values for the type of report I am requesting. For example, if I have budget
> > > > data but no actual data for an item, this item still shows up when I request
> > > > the report for actual data. Is there any way to prevent these rows from
> > > > appearing?
> >
> > > I'm not familiar w/MDX; however, you should be able to filter the
> > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > is helpful.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. SQL Server Developer
> It seems like you might not have set the 'Value' part of the filter to
> an integer value. By default, the filter sets all numbers in the
> 'Value' column to strings unless you use an = sign before it. You
> might want to try something like: in the 'Expression' column enter:
> =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> the 'Value' column enter: =0
> If this does not correct the problem, you will most likely need to
> convert the results of 'Fields!GrossSales.Value' to an integer: via
> something like: CInt(Fields!GrossSales.Value).
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 5, 7:16 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> One more question....For some reason the Table filter list has the and/or
> column on the selection grayed out. I would like to be able to say "sales OR
> pounds" not =0. Do you have any idea why the and/or column is not available
> under the table filter properties?
> Thank you,
> PB
> "EMartinez" wrote:
> > On Mar 4, 8:54 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > I tried the following filter "=Fields!GrossSales.Value > 0". I get an error
> > > message that says "Processing of filter can not be performed. Please check
> > > the data type value retuend by the filter expression." Can you tell me what
> > > this means and how to set up the statement properly. Thank you for your
> > > help. I'm learning this tool on a "need to know" basis and I have 60 reports
> > > to create w/o any real training.
> > > "EMartinez" wrote:
> > > > On Mar 2, 11:33 am, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> > > > > I have a report created using MDX imported from another application. In
> > > > > order to provide flexibility, this MDX carries in several fields that are not
> > > > > necessarily used when the report is created. (The report prompts for
> > > > > parameters which determine whether it should be created using budget or
> > > > > actual data.) Because of this, I am getting rows of data where there are no
> > > > > values for the type of report I am requesting. For example, if I have budget
> > > > > data but no actual data for an item, this item still shows up when I request
> > > > > the report for actual data. Is there any way to prevent these rows from
> > > > > appearing?
> > > > I'm not familiar w/MDX; however, you should be able to filter the
> > > > results by something like Sum(Fields!ActualData.Value) > 0. Hope this
> > > > is helpful.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. SQL Server Developer
> > It seems like you might not have set the 'Value' part of the filter to
> > an integer value. By default, the filter sets all numbers in the
> > 'Value' column to strings unless you use an = sign before it. You
> > might want to try something like: in the 'Expression' column enter:
> > =Fields!GrossSales.Value; in the 'Operator' column select '>'; and in
> > the 'Value' column enter: =0
> > If this does not correct the problem, you will most likely need to
> > convert the results of 'Fields!GrossSales.Value' to an integer: via
> > something like: CInt(Fields!GrossSales.Value).
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
You're welcome. Glad to be of assistance. I have not been able to get
the filter options to allow 'or' in my experiences so far. You might
need to have the filtering in the query or stored procedure that is
sourcing the report to allow for more flexibility. Just a thought.
Regards,
Enrique Martinez
Sr. SQL Server Developer
Monday, March 19, 2012
exclude blank records
I have created a script that returns every column and row that is queried
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com
and some of the fields are blank. I want to only return the fields that are
populated. Below is the script. Any advice would be appreciated:
SELECT Relation.xparent_prov_id,
Relation.Parent,
Provider.DataSource_ID as prov_datasource_ID,
Provider.Provider_Name,
Provider.Provider_Type,
Provider.Degree_Type,
FEIProviderStatus.Status,
Provider.DataSource_ID,
Provider.City,
Provider.State,
evClinician_Profile.arabic
CASE
WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
ELSE ''
END AS Arabic,
CASE
WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
ELSE ''
END AS Chinese,
CASE
WHEN evClinician_Profile.french = 'Y' THEN 'French'
ELSE ''
END AS French,
CASE
WHEN evClinician_Profile.german = 'Y' THEN 'German'
ELSE ''
END AS German,
CASE
WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
ELSE ''
END AS Hebrew,
CASE
WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
ELSE ''
END AS Italian,
CASE
WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
ELSE ''
END AS Japanese,
CASE
WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
ELSE ''
END AS Russian,
CASE
WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
ELSE ''
END AS Spanish
INTO #TEMP
FROM Provider
INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
INNER JOIN evClinician_Profile ON Provider.Provider_Key =
evClinician_Profile.RelMan_Key
INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
FEIProviderStatus.ProviderID
SELECT * FROM #TEMP
DROP TABLE #TEMP
Message posted via http://www.webservertalk.comAs a general suggestion, you can use a WHERE clause in your query like:
WHERE '' NOT IN ( Arabic, Chinese, ... Spanish )
Anith|||What if one of the fields is blank and others are populated, do you want to
reject the whole row because of this?
If so, specify a WHERE clause as mentioned in an earlier reply.
Ilyan Mishiyev
IGM Consulting Corporation
Enterprise Web Solutions
www.igmcc.com
"Jay via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:c3177033e42f430f85a389ecc7a323f6@.SQ
webservertalk.com...
>I have created a script that returns every column and row that is queried
> and some of the fields are blank. I want to only return the fields that
> are
> populated. Below is the script. Any advice would be appreciated:
> SELECT Relation.xparent_prov_id,
> Relation.Parent,
> Provider.DataSource_ID as prov_datasource_ID,
> Provider.Provider_Name,
> Provider.Provider_Type,
> Provider.Degree_Type,
> FEIProviderStatus.Status,
> Provider.DataSource_ID,
> Provider.City,
> Provider.State,
> evClinician_Profile.arabic
> CASE
> WHEN evClinician_Profile.arabic = 'Y' THEN 'Arabic'
> ELSE ''
> END AS Arabic,
> CASE
> WHEN evClinician_Profile.chinese = 'Y' THEN 'Chinese'
> ELSE ''
> END AS Chinese,
> CASE
> WHEN evClinician_Profile.french = 'Y' THEN 'French'
> ELSE ''
> END AS French,
> CASE
> WHEN evClinician_Profile.german = 'Y' THEN 'German'
> ELSE ''
> END AS German,
> CASE
> WHEN evClinician_Profile.hebrew = 'Y' THEN 'Hebrew'
> ELSE ''
> END AS Hebrew,
> CASE
> WHEN evClinician_Profile.italian = 'Y' THEN 'Italian'
> ELSE ''
> END AS Italian,
> CASE
> WHEN evClinician_Profile.japanese ='Y' THEN 'Japanese'
> ELSE ''
> END AS Japanese,
> CASE
> WHEN evClinician_Profile.russian = 'Y' THEN 'Russian'
> ELSE ''
> END AS Russian,
> CASE
> WHEN evClinician_Profile.spanish = 'Y' THEN 'Spanish'
> ELSE ''
> END AS Spanish
> INTO #TEMP
> FROM Provider
> INNER JOIN Relation ON Provider.DataSource_ID = Relation.datasource_id
> INNER JOIN evClinician_Profile ON Provider.Provider_Key =
> evClinician_Profile.RelMan_Key
> INNER JOIN FEIProviderStatus ON Provider.DataSource_ID =
> FEIProviderStatus.ProviderID
> SELECT * FROM #TEMP
> DROP TABLE #TEMP
> --
> Message posted via http://www.webservertalk.com|||No, if the field is populated I want those to return those records.
Message posted via http://www.webservertalk.com|||That doesn't answer Ilyan's question. For example, given the following:
CREATE TABLE T1 (x INTEGER NOT NULL PRIMARY KEY, y CHAR(1) NULL, z
CHAR(1) NULL)
INSERT INTO T1 VALUES (1,'A',NULL)
INSERT INTO T1 VALUES (2,NULL,'B')
INSERT INTO T1 VALUES (3,'A','B')
You could exclude rows where either Y or Z is NULL:
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
AND z IS NOT NULL
or only where BOTH are NULL
SELECT x,y,z
FROM T1
WHERE y IS NOT NULL
OR z IS NOT NULL
You said you wanted to return "fields that are populated". Does that
mean you want to see a different number of columns depending on what
data exists? You'll have to do that either client-side, or with Dynamic
SQL or using a set of IF statements to cover all the various cases. A
static query always returns the same number of columns - it can't be
changed at runtime.
David Portas
SQL Server MVP
--|||I think I solved the issue by using:
WHERE LEN (fieldname) < 0
I did this code for each of the fields that I was querying in the sp. Is
this the most effeicient way to do this?
Message posted via http://www.webservertalk.com|||This achieves nothing except exclude the rows where fieldname is NULL
so you might as well write:
WHERE fieldname IS NOT NULL
Notice that NULL is not the same as an empty string. If you want to
exclude empty strings as well then you can do:
WHERE fieldname > ''
David Portas
SQL Server MVP
--|||You're right I got the same number of rows. Thanks for the feedback.
Message posted via http://www.webservertalk.com
exclude a field from a wildcard search (was "A simple question! (heeelp)")
Hey guys, i'm new in SQL and i have a very enervating problem.
I have a form and with some fields.I want users to be able to write just a part of a word in order to search for it (except the first field) .So,as you can see in the querie below i have put the "%" symbol as i know in all fields except tha first one.
So,this querie works only for the first field. If i put the "%" in the first field too the all the fields are ok.
The point is that I don't want the first field to search that way (with "%")...What should i do?
Thanks in advance!
strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID") & "' and "
strQ = strQ & " Surname LIKE '%" & Request.Form.Item("surname") & "%' and"
strQ = strQ & " Name LIKE '%" & Request.Form.Item("name") & "%' and"
strQ = strQ & " IdentityNo LIKE '%" & Request.Form.Item("IdentityNo") & "%' and"
strQ = strQ & " Address LIKE '%" & Request.Form.Item("Address") & "%' and"
strQ = strQ & " Area LIKE '%" & Request.Form.Item("Area") & "%' and"
strQ = strQ & " Zip LIKE '%" & Request.Form.Item("Zip") & "%' and"
strQ = strQ & " PhoneNo LIKE '%" & Request.Form.Item("PhoneNo") & "%' and"
strQ = strQ & " CellNo LIKE '%" & Request.Form.Item("CellNo") & "%' and"
strQ = strQ & " Email LIKE '%" & Request.Form.Item("Email") & "%' and"
strQ = strQ & " AddrNo LIKE '%" & Request.Form.Item("AddrNo") & "%';"Rather than put every column in the query you should perhaps consider testing if the requestform value has a value in. it is pointless including columns in the where clause if your setting will return all / any rows already.
eg ' not knowing the language you are using, but guessing at VB or one of its realtives...
strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID")
if len(request.form.item("surname"))>0 then
strQ = strQ & " AND Surname LIKE '%" & Request.Form.Item("surname") & "%'"
endif
if len(request.form.item("name"))>0 then
strQ = strQ & " AND Name LIKE '%" & Request.Form.Item("name") & "%'"
endif
etc.....
your code as written suggests it is expecting the user to place a value in each and every setting. You also might consider whether you are looking for an 'AND' or an 'OR' constraint ie are you looking for every occurance of Surname=Smith AND name=John or every row containing some with a Surname=Smith OR name=John
HTH|||I use "and" cause i want the search function to operate if i have fill in one or more fields.But the problem it's not there.This works fine.|||And somethings else
how can i see the results of COUNT?
strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"
P.S: I use vbscript and javascript|||And somethings else
how can i see the results of COUNT?
strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"
P.S: I use vbscript and javascript
Ok i found that (expr1000)!!!!
I have a form and with some fields.I want users to be able to write just a part of a word in order to search for it (except the first field) .So,as you can see in the querie below i have put the "%" symbol as i know in all fields except tha first one.
So,this querie works only for the first field. If i put the "%" in the first field too the all the fields are ok.
The point is that I don't want the first field to search that way (with "%")...What should i do?
Thanks in advance!
strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID") & "' and "
strQ = strQ & " Surname LIKE '%" & Request.Form.Item("surname") & "%' and"
strQ = strQ & " Name LIKE '%" & Request.Form.Item("name") & "%' and"
strQ = strQ & " IdentityNo LIKE '%" & Request.Form.Item("IdentityNo") & "%' and"
strQ = strQ & " Address LIKE '%" & Request.Form.Item("Address") & "%' and"
strQ = strQ & " Area LIKE '%" & Request.Form.Item("Area") & "%' and"
strQ = strQ & " Zip LIKE '%" & Request.Form.Item("Zip") & "%' and"
strQ = strQ & " PhoneNo LIKE '%" & Request.Form.Item("PhoneNo") & "%' and"
strQ = strQ & " CellNo LIKE '%" & Request.Form.Item("CellNo") & "%' and"
strQ = strQ & " Email LIKE '%" & Request.Form.Item("Email") & "%' and"
strQ = strQ & " AddrNo LIKE '%" & Request.Form.Item("AddrNo") & "%';"Rather than put every column in the query you should perhaps consider testing if the requestform value has a value in. it is pointless including columns in the where clause if your setting will return all / any rows already.
eg ' not knowing the language you are using, but guessing at VB or one of its realtives...
strQ = "SELECT * FROM CustInf WHERE CustID LIKE '" & Request.Form.Item("CustID")
if len(request.form.item("surname"))>0 then
strQ = strQ & " AND Surname LIKE '%" & Request.Form.Item("surname") & "%'"
endif
if len(request.form.item("name"))>0 then
strQ = strQ & " AND Name LIKE '%" & Request.Form.Item("name") & "%'"
endif
etc.....
your code as written suggests it is expecting the user to place a value in each and every setting. You also might consider whether you are looking for an 'AND' or an 'OR' constraint ie are you looking for every occurance of Surname=Smith AND name=John or every row containing some with a Surname=Smith OR name=John
HTH|||I use "and" cause i want the search function to operate if i have fill in one or more fields.But the problem it's not there.This works fine.|||And somethings else
how can i see the results of COUNT?
strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"
P.S: I use vbscript and javascript|||And somethings else
how can i see the results of COUNT?
strQ = "SELECT COUNT(CustID)FROM Movies WHERE CustID is not NULL"
P.S: I use vbscript and javascript
Ok i found that (expr1000)!!!!
Friday, February 24, 2012
excel vba to generate flat file definition
What object do I reference to use SSIS from Excel. I want to generate a flat file definition based on Excel. I have a lot of fields to import and I don't feel like creating them as flat file columns. I have a few tables and I get the source file format from the vendor in an Excel format. What I would like to do is generate a flat file connection in an empty package using VBA.
I don't think this is possible. VBA is a COM based environment whereas the SSIS API is dotnet.
I stand to be corrected. If there is a way of calling dotnet APIs from VBA then I don't know about it.
-Jamie
|||As Jamie notes, our documentation is aimed at using the SSIS API through managed code. Using the native API is unsupported. You might be able to work backwards from the managed examples, though, if you're feeling adventurous. I'd suggest you start here: http://msdn2.microsoft.com/en-us/library/ms345167.aspx
Excel Transformation Problems
Hi guys,
I have encountered inconsistencies using the excel data source. When I try to transform some of the fields in excel, though it has values, it sometimes returns null. For example, I have this column below:
X
--
50
30
20
70
60
Sometimes it works, meaning I can get all the values, sometimes I cant, which means it returns all null. Anybody encountered this issue before?
By the way, it only happens if there is some empty values within the working cell.
Thanks.
Cheers,
Ryan Tan
Your column contains what the driver has deemed to be a mix of numeric and text values. It has ruled in favor of the majority (numeric?) and returns other cell values as null. This is an age-old issue with the Excel driver. Please see the BOL topic on the Excel Source for workarounds for common issues.
I can email you a more detailed known issues document (than I was able to include in BOL) tomorrow if you'll remind me, please, at douglasl@.microsoft.com.
-Doug
|||Have a look at the article below. This explains the age-old issue with Excel.
http://www.sqldts.com/default.aspx?254
Thanks
Sutha
Sunday, February 19, 2012
Excel Source dt_ntext problems
Hi:
I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.
If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.
If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).
I worked around by adding a dummy first row with >255 chars.
Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.
Is there some clean way to get around this problem?
TIA
Kar
Hi Karfast
For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic
Follw these instructions carefully:
1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)
2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length
Thanks
Subhash Subramanyam
|||Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.
Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).
HTH.
|||Thanks, Bob.
I tried:
Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.
I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.
So I guess the dirty workaround is the only way :-(
Kar
Subscribe to:
Posts (Atom)