Load Excel Data using DTS NULL error

Hi!
I am loading data from Excel file, with three columns Varchar(5), Float and
Char(6).
When I execute DTS I am getting few NULL values for first column
(Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
Preview, it is also showing Null values for that.
Thanks,
Sam
Not sure what your question us here? Do you mean there arent really nulls
but sql thinks there is? Do you mean there are null values and sql wont
allow them?
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> Hi!
> I am loading data from Excel file, with three columns Varchar(5), Float
> and
> Char(6).
> When I execute DTS I am getting few NULL values for first column
> (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> Preview, it is also showing Null values for that.
> Thanks,
> Sam
|||Chris,
Thanks for the reply. This problem got fix temporarily, as the person who
gave me this excel file, he said there was some formula involved. Anyway the
problem I am describing below:
Example:
Column1 - Location list 1, 2, 3, ...10.
Column 2 - Location Name A, B, C,...D
I tried to load both columns and found in the preview that 3 and 7 are
giving NULL values.
Thanks,
Sam
"ChrisR" wrote:

> Not sure what your question us here? Do you mean there arent really nulls
> but sql thinks there is? Do you mean there are null values and sql wont
> allow them?
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
>
>
|||In your DTS Package, use an Active X Script to replace the NULL's.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...[vbcol=seagreen]
> Chris,
> Thanks for the reply. This problem got fix temporarily, as the person who
> gave me this excel file, he said there was some formula involved. Anyway
> the
> problem I am describing below:
> Example:
> Column1 - Location list 1, 2, 3, ...10.
> Column 2 - Location Name A, B, C,...D
> I tried to load both columns and found in the preview that 3 and 7 are
> giving NULL values.
> Thanks,
> Sam
>
> "ChrisR" wrote:
|||Chris,
I tried with SQL Task and it did work.
Thanks,
Sam
"ChrisR" wrote:

> In your DTS Package, use an Active X Script to replace the NULL's.
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
>
>

Load Excel Data using DTS NULL error

Hi!
I am loading data from Excel file, with three columns Varchar(5), Float and
Char(6).
When I execute DTS I am getting few NULL values for first column
(Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
Preview, it is also showing Null values for that.
Thanks,
SamNot sure what your question us here? Do you mean there arent really nulls
but sql thinks there is? Do you mean there are null values and sql wont
allow them?
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> Hi!
> I am loading data from Excel file, with three columns Varchar(5), Float
> and
> Char(6).
> When I execute DTS I am getting few NULL values for first column
> (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> Preview, it is also showing Null values for that.
> Thanks,
> Sam|||Chris,
Thanks for the reply. This problem got fix temporarily, as the person who
gave me this excel file, he said there was some formula involved. Anyway the
problem I am describing below:
Example:
Column1 - Location list 1, 2, 3, ...10.
Column 2 - Location Name A, B, C,...D
I tried to load both columns and found in the preview that 3 and 7 are
giving NULL values.
Thanks,
Sam
"ChrisR" wrote:
> Not sure what your question us here? Do you mean there arent really nulls
> but sql thinks there is? Do you mean there are null values and sql wont
> allow them?
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> > Hi!
> > I am loading data from Excel file, with three columns Varchar(5), Float
> > and
> > Char(6).
> > When I execute DTS I am getting few NULL values for first column
> > (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> > Preview, it is also showing Null values for that.
> >
> > Thanks,
> > Sam
>
>|||In your DTS Package, use an Active X Script to replace the NULL's.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
> Chris,
> Thanks for the reply. This problem got fix temporarily, as the person who
> gave me this excel file, he said there was some formula involved. Anyway
> the
> problem I am describing below:
> Example:
> Column1 - Location list 1, 2, 3, ...10.
> Column 2 - Location Name A, B, C,...D
> I tried to load both columns and found in the preview that 3 and 7 are
> giving NULL values.
> Thanks,
> Sam
>
> "ChrisR" wrote:
>> Not sure what your question us here? Do you mean there arent really nulls
>> but sql thinks there is? Do you mean there are null values and sql wont
>> allow them?
>>
>> "Sam" <Sam@.discussions.microsoft.com> wrote in message
>> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
>> > Hi!
>> > I am loading data from Excel file, with three columns Varchar(5), Float
>> > and
>> > Char(6).
>> > When I execute DTS I am getting few NULL values for first column
>> > (Varchar(5)). There is a appropriate data in Excel file. I checked in
>> > DTS
>> > Preview, it is also showing Null values for that.
>> >
>> > Thanks,
>> > Sam
>>|||Chris,
I tried with SQL Task and it did work.
Thanks,
Sam
"ChrisR" wrote:
> In your DTS Package, use an Active X Script to replace the NULL's.
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
> > Chris,
> > Thanks for the reply. This problem got fix temporarily, as the person who
> > gave me this excel file, he said there was some formula involved. Anyway
> > the
> > problem I am describing below:
> > Example:
> > Column1 - Location list 1, 2, 3, ...10.
> > Column 2 - Location Name A, B, C,...D
> > I tried to load both columns and found in the preview that 3 and 7 are
> > giving NULL values.
> >
> > Thanks,
> > Sam
> >
> >
> > "ChrisR" wrote:
> >
> >> Not sure what your question us here? Do you mean there arent really nulls
> >> but sql thinks there is? Do you mean there are null values and sql wont
> >> allow them?
> >>
> >>
> >> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> >> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> >> > Hi!
> >> > I am loading data from Excel file, with three columns Varchar(5), Float
> >> > and
> >> > Char(6).
> >> > When I execute DTS I am getting few NULL values for first column
> >> > (Varchar(5)). There is a appropriate data in Excel file. I checked in
> >> > DTS
> >> > Preview, it is also showing Null values for that.
> >> >
> >> > Thanks,
> >> > Sam
> >>
> >>
> >>
>
>

Load Excel Data using DTS NULL error

Hi!
I am loading data from Excel file, with three columns Varchar(5), Float and
Char(6).
When I execute DTS I am getting few NULL values for first column
(Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
Preview, it is also showing Null values for that.
Thanks,
SamNot sure what your question us here? Do you mean there arent really nulls
but sql thinks there is? Do you mean there are null values and sql wont
allow them?
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
> Hi!
> I am loading data from Excel file, with three columns Varchar(5), Float
> and
> Char(6).
> When I execute DTS I am getting few NULL values for first column
> (Varchar(5)). There is a appropriate data in Excel file. I checked in DTS
> Preview, it is also showing Null values for that.
> Thanks,
> Sam|||Chris,
Thanks for the reply. This problem got fix temporarily, as the person who
gave me this excel file, he said there was some formula involved. Anyway the
problem I am describing below:
Example:
Column1 - Location list 1, 2, 3, ...10.
Column 2 - Location Name A, B, C,...D
I tried to load both columns and found in the preview that 3 and 7 are
giving NULL values.
Thanks,
Sam
"ChrisR" wrote:

> Not sure what your question us here? Do you mean there arent really nulls
> but sql thinks there is? Do you mean there are null values and sql wont
> allow them?
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:FF87A229-33D7-460C-8F0E-162F7F0F6AEC@.microsoft.com...
>
>|||In your DTS Package, use an Active X Script to replace the NULL's.
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...[vbcol=seagreen]
> Chris,
> Thanks for the reply. This problem got fix temporarily, as the person who
> gave me this excel file, he said there was some formula involved. Anyway
> the
> problem I am describing below:
> Example:
> Column1 - Location list 1, 2, 3, ...10.
> Column 2 - Location Name A, B, C,...D
> I tried to load both columns and found in the preview that 3 and 7 are
> giving NULL values.
> Thanks,
> Sam
>
> "ChrisR" wrote:
>|||Chris,
I tried with SQL Task and it did work.
Thanks,
Sam
"ChrisR" wrote:

> In your DTS Package, use an Active X Script to replace the NULL's.
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:022D6103-CB66-468A-8063-CEB75BB9FAEF@.microsoft.com...
>
>sql

little problem..

hey guys, i have a little problem:

i use ASP with MSSQL and i have a query that adds these values to the database:

values(' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')

when a value is empty, the fields are not filled
but when a value contains a single quote ('), that quote ends the value:

values(' hel'lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')

so my question is, how can i make the quote (') in hel'lo .. invisible or something, so MSSQL wont see it as the end of the value.

(im MYSQL, you can just place a (\) in front of the (') but that doenst work in MSSQL...)use like this :

values(' hel''lo ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')

-Rohit|||Use one more single quote as the escape character.|||thanx lads! :)

Listing Db values

Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.try this one
select sysobjects.name as Table_Name,syscolumns.name as Column_Name,systypes.name as Data_Type from sysobjects
join syscolumns on sysobjects.id=syscolumns.id
join systypes on syscolumns.xtype=systypes.xtype and systypes.status=typestat
where sysobjects.type='u'

Originally posted by kixer
Hi, I want to make a logfile where i store all tables, collnames and values of a specified database. Which statement can I use in SQLserver or Oracle? I already found the following statements:

Oracle:
select * from all_tables
select * from user_tables

SQLserver:
select * from sysobjects where type'='U'

So getting the tablenames isn't the problem. The question is how the get the matching columns with their type and value.

Tnx.|||Thanx! ;) Now I know the objectnames. All I have to do now is to make a nice treeview with the generated values, so i can log some sort of a dictionary.

listbox selected values

Hi. With VWD i've produced the following code.
<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID)">
<SelectParameters>
<asp:ControlParameterControlID="ListBox1"Name="liedID"PropertyName="SelectedValue"Type="Int16"/>
But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?
Thanks, Kin Wei.

That's not really an easy thing to do. There are a number of "issues" to work around.
1) The WHERE clause would need to be changed to an equate to something like IN.
2) Using IN, you can't use a parameter to represent a list of values.
3) There is no way to get the list control to give you a comma delimited list of selected values.
You can get around the #2 issue by dynamically creating an executing SQL using the EXEC command like this:
DECLARE @.sql varchar(8000)
SET @.sql = 'SELECT * FROM ibs WHERE liedID IN (' + @.liedID + ')'
EXEC(@.sql)
You can get around #3 by building the string yourself by:
1)Use autopostbacks to keep track of the selected values, and store them in a hidden field or a session variable as a comma delimited string.
or
2)On postback, get the list of selectedindexes via listbox1.GetSelectedIndices like this:
dim mystring as string=""
for each x as Integer in listbox1.GetSelectedIndices
mystring &= ",'" & cstr(Listbox1.Items(x).Value) & "'"
next
mystring=mystring.trim(",")
HiddenField.text=mystring
|||Thanks for your post. But I'm not really good at this. Where in the code do I have to make the EXEC command?
<%@. Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns:t ="urn:schemas-microsoft-com:time">
<head runat="server">
<?import namespace="t"
implementation="#default#time2">
<style>
.time {behavior: url(#default#time2);}
</style>
<title>IBSTEST</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Panel ID="Panel1" runat="server" Height="50px" Width="125px" Visible="true">
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] ORDER BY [liedTitel]"></asp:SqlDataSource>
<asp:ListBox ID="ListBox1" runat="server" DataSourceID="SqlDataSource2" DataTextField="liedTitel"
DataValueField="liedID" Font-Names="Arial" Font-Size="X-Small" Height="300px"
SelectionMode="Multiple" Width="350px"></asp:ListBox><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Play" OnClick="Button1_Click" Height="25px" Width="75px" /></asp:Panel>
<br />
<asp:Panel ID="Panel2" runat="server" Height="50px" Width="349px" Visible="false">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"
SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @.liedID) ORDER BY NEWID()">
<SelectParameters>
<asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue"
Type="Int16" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<t:seq repeatCount="1">
</HeaderTemplate>
<ItemTemplate>
<t:par>
<t:audio dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' src='<%# DataBinder.Eval(Container.DataItem, "liedID") %><%# DataBinder.Eval(Container.DataItem, "liedExtensie") %>' type='<%# DataBinder.Eval(Container.DataItem, "liedType") %>' />
<div dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display">
Titel: <%# DataBinder.Eval(Container.DataItem, "liedTitel") %><br />
Artiest: <%# DataBinder.Eval(Container.DataItem, "liedArtiest") %></div>
<img dur='<%# DataBinder.Eval(Container.DataItem, "liedDuur") %>s' class="time" timeAction="display" src='<%# DataBinder.Eval(Container.DataItem, "liedImage") %>.jpg' />
</t:par>
</ItemTemplate>
<FooterTemplate>
</t:seq>
</FooterTemplate>
</asp:Repeater>
<br />
<br />
<asp:Button ID="Button2" runat="server" Height="25px" OnClick="Button2_Click" Text="Stop"
Width="75px" /></asp:Panel>
<br />
</div>
</form>
</body>
</html>
and the .cs code is
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Panel1.Visible = false;
Panel2.Visible = true;
}
protected void Button2_Click(object sender, EventArgs e)
{
Panel1.Visible = true;
Panel2.Visible = false;
}
}
After your post, I think it's clear what have to be done. But I don't know where to put the code.
Thank you very much,
Kin Wei

ListBox and Table Values

Hi

got small problems with the table values. I got three rows in a table

Index (int)
Product (varchar)
Price (float)

I could successfully connect to the database. I also get the values but somehow the ListBox don't show them...

Anyone can help me please ?

CODE

{
CDBVariant value;
char sql_statement [2048] = "";

//CDatabase object "db" created to connect database
CDatabase db;
db.OpenEx(_T("DSN=Beauty"),CDatabase::noOdbcDialog);

//CRecordset object "rs" created to access and manipulate database records.
CRecordset rs(&db);
strcpy(sql_statement,_T("SELECT * FROM TestTabelle"));
rs.Open(CRecordset::forwardOnly,sql_statement);

//Get quantity from Database
int n = rs.GetODBCFieldCount( );

while(!rs.IsEOF())
{
for( int i = 0; i < n; i++ )

{
rs.GetFieldValue("index",value);
m_Buy_List.InsertItem(i,LPCTSTR(value.m_lVal));

rs.GetFieldValue("product",value.m_pstring);
m_Buy_List.SetItemText(i,2,LPCTSTR(value));

rs.GetFieldValue("price",value);
m_Buy_List.SetItemText(i,3,LPCTSTR(value.m_fltVal) );

rs.MoveNext( );
}
}This is a VC++ code problem, I presume. Probably you should try some VC++ forums, for ex: www.codeguru.com

Best regards!sql

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

List() Function

Sybase has implemented a useful aggregate function called list() which takes
a column and returns all values in a comma delimited string. In Oracle10g,
there is a Java solution to duplicate this aggregation function. Now I have
to do the same in SQLServer2005. I found the UDA (User Defined Aggregate)
support in MSSQL2005 and some sample codes in C#. Has anyone done this
before? If someone can upload a precompiled module, it will save me some
effort. TIA.
I am downloading .Net Framework 2.0 SDK right now and hopefully it contains
a C# compiler. Otherwise, I have to buy a copy of Visual Studio .Net just
for this function.Why do you think you need to use a UDA aggregate for this? Seems like
overkill, since several other methods already exist and are widely
available. You may be able to write something that performs a few
milliseconds faster, but it will come at considerable development cost, and
there is plenty of room for new problems.
"mason" <masonliu@.msn.com> wrote in message
news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Sybase has implemented a useful aggregate function called list() which
> takes a column and returns all values in a comma delimited string. In
> Oracle10g, there is a Java solution to duplicate this aggregation
> function. Now I have to do the same in SQLServer2005. I found the UDA
> (User Defined Aggregate) support in MSSQL2005 and some sample codes in C#.
> Has anyone done this before? If someone can upload a precompiled module,
> it will save me some effort. TIA.
> I am downloading .Net Framework 2.0 SDK right now and hopefully it
> contains a C# compiler. Otherwise, I have to buy a copy of Visual Studio
> .Net just for this function.|||I know. This is a migration project. If I can duplicate as much as possible
at the backend, I'll save tremendous efforts at the frontend. Besides, there
is only one aggregate function I like to create and I found some sample
source codes, it shouldn't be too bad. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23hYedq$NGHA.740@.TK2MSFTNGP12.phx.gbl...
> Why do you think you need to use a UDA aggregate for this? Seems like
> overkill, since several other methods already exist and are widely
> available. You may be able to write something that performs a few
> milliseconds faster, but it will come at considerable development cost,
> and there is plenty of room for new problems.
>
>
> "mason" <masonliu@.msn.com> wrote in message
> news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Check out 'For Xml Path' in:
http://www.aspfaq.com/show.asp?id=2529
Her is more from Tony Rogerson SQL Server MVP:

>
In SQL Server 2005 we can very simply use some of the new XML features to
get what we want...
select distinct type,
( select name + ', ' as [text()]
from sys.objects s
where s.type = so.type
order by name
for xml path( '' )
) as concatenated_name
from sys.objects so
order by type
This is documented in books online, look up 'Using PATH mode'
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a685a9ad-3d28-4596-aa72-119202df3976.htm)[
color=darkred]
>[/color]
www.rac4sql.net|||Thanks. I read about these solutions on the web. The issue is the list() has
been a very popular function to be called in embedded SQL statements by our
apps and in some stored procedures. If I do not implement this, we have to
hunt down those codes and perform IF ELSE logic, very tedious work. If one
day, a customer insists to use DB2, we have to go thru this process again.
Therefore, finding backend portable solution is usually the most cost
efficient for us.
"05ponyGT" <noname@.overwood.com> wrote in message
news:ekltQ0$NGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Check out 'For Xml Path' in:
> http://www.aspfaq.com/show.asp?id=2529
> Her is more from Tony Rogerson SQL Server MVP:
>
> In SQL Server 2005 we can very simply use some of the new XML features to
> get what we want...
> select distinct type,
> ( select name + ', ' as [text()]
> from sys.objects s
> where s.type = so.type
> order by name
> for xml path( '' )
> ) as concatenated_name
> from sys.objects so
> order by type
> This is documented in books online, look up 'Using PATH mode'
> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a685a9ad-3d28-4596-aa72-1
19202df3976.htm)
> www.rac4sql.net
>|||Oh my, a helpful post. :)

> 05ponyGT
Nice ride.|||> Oh my, a helpful post. :)

> Nice ride.
Vert.
3.73 tush,bassani axle backs and x-pipe,jba headers,
aem cai,flashed.
BAD A$& Car
:P|||05ponyGT <nospam@.nospam> wrote:
>
> Vert.
> 3.73 tush,bassani axle backs and x-pipe,jba headers,
> aem cai,flashed.
> BAD A$& Car
> :P
I'm impressed! :)|||I have implemented list() UDA function in MSSQL2005 successfully. The source
codes are adapted from Anthony Trudeau's blog page. Here are the steps if a
nyone's interested.
1. Download .Net Framework 2.0 SDK and/or Visual C# 2005 Express. Both are f
ree. The purpose is to get the C# compiler (csc.exe) and the latter gives yo
u the IDE to config the module version info if you care. Create a class libr
ary project and compile the C# source codes from Anthony Trudeau's example (
make appropriate modifications as I did if necessary). You will get a DLL. T
o skip this step, you can use the DLL I attached to this msg.
2. Run the following SQL to create the list() aggregate function:
CREATE ASSEMBLY UDAFunctions FROM 'C:\TEMP\ListClassLibrary.dll'
go
CREATE AGGREGATE list(@.value NVARCHAR(4000)) RETURNS NVARCHAR(4000)
EXTERNAL NAME [UDAFunctions].[SqlAggregateFunctions.List]
go
Now, you can issue SQLs like the following to get a comma delimited value li
st such as 2001,2003,2006 and CA,TX,NY,IL,FL.
SELECT dbo.list(mycol) from mytable WHERE ...
The only limitation is, as in Oracle10g, that ORDER BY clause is not support
ed, which you can do in Sybase.
"mason" <masonliu@.msn.com> wrote in message news:uqeTYn$NGHA.1028@.TK2MSFTNGP11.phx.gbl...[c
olor=darkred]
> Sybase has implemented a useful aggregate function called list() which tak
es
> a column and returns all values in a comma delimited string. In Oracle10g,
> there is a Java solution to duplicate this aggregation function. Now I hav
e
> to do the same in SQLServer2005. I found the UDA (User Defined Aggregate)
> support in MSSQL2005 and some sample codes in C#. Has anyone done this
> before? If someone can upload a precompiled module, it will save me some
> effort. TIA.
>
> I am downloading .Net Framework 2.0 SDK right now and hopefully it contain
s
> a C# compiler. Otherwise, I have to buy a copy of Visual Studio .Net just
> for this function.
>[/color]|||>> If I do not implement this, we have to hunt down those codes and perform
IF ELSE logic, very tedious work. If one day, a customer insists to use DB2
, we have to go thru this process again. Therefore, finding backend portabl
e solution is usually the m
ost cost efficient for us. <<
Think of this as how God punishes programmers who do not write portable
code in the firsts place. Your backend solution is probably going to
involve different proprietary code in the target product. The reason I
say that is that LIST() is a way to screw up 1NF, so it is not going to
be popular with RDBMS people and it is not part of the Standards. It
was proposed at one time and was voted down.
What you are setting yourself up for is having to maintain (n) code
bases instead of one. Bite the bullet and do it right. Oh, and beat
your developers who wrote you into this corner.
Based on a few decades of patching bad SQL, I am not sure that you will
need IF-THEN-ELSE code in your SQL, if you do it right.

List of values that make SQL Blow

I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion

Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.

IE (ticks, #, :, -,)

I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.

I know this is a nightmare but it is the contraint I am working under.Not sure... but I recently learnt that .NET doesn't like column names starting with a numeric character so worth bearing in mind if that is your FE.|||I am not sure such a list exists, as technically SQL Server will accept anything between brackets, so long as the name is unique within the table.

create table test1
([(ticks, #, :, -,)] int, -- Given example
[~`!@.#$%^&*()_+] int) -- Top row of the keyboard.

select *
from test1|||I would be tempted to accept alpha numerics only myself just to be sure (but I am a bit of pessimist with these sorts of things). There is also the "reserved words" issue.

Actually - I imagine square brackets would need to be escaped (?).|||Sounds like dynamic creation to me...I run herd over anything getting created in oneof my databases|||Looks like only the close bracket needs to be escaped with an extra close bracket.

drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)

select *
from test1

Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.|||Looks like only the close bracket needs to be escaped with an extra close bracket.

drop table test1
create table test1
([create table test2 (oops int not null)] int,
[]][] int)

select *
from test1

Personally, I would fight this kind of system in my shop tooth and nail, but that was not the original question.

I'm old school myself, try and avoid special chars of any type, except _ in database object nams.|||I thank you all for your input.

I have fought tooth and nail My boss has said to come up with a solution. Mangers can be so OBTUSE

I have determined that the business can add alpha numeric with _
starting alpha ending alpha numeric.

Users will create these in a table that will define relationship then a process will auto generate a change control that will be added to physical structure of the database by the DBA team.

No dynamic running of an alter statment.|||Horrible.

You should rename this thread "List of managers that make SQL blow."|||I awlways wanted a list that would make my girlfr...umm never mind|||OMG Bret, if you're having trouble when she's a girlfriend...

then you are doomed after marriage...then you can get her one of those custom-lettered necklaces that say "abandon all hope, ye who..." ;)

(sorry, couldn't resist)|||Yes Brett. Listen to the advice of Mr. Romance.|||Yes Brett, listen to the advice from Mr Charm about listening to the advice from Mr Romance ;)|||Ouch! :shocked:|||i'm not sure how any of this is relavant to sql server??|||That's because you aren't familiar with the hidden intricacies of the database engines. It's actually pretty sexy in there.

list of strings passed into a parameter

I am trying to pass multiple values as parameters into my update command:

UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE ((EmployeeNumber IN (@.selectedusersparam)));

I develop my parameter (@.selectedusersparam) using the following subroutine:

PrivateSub btnAddUsersToDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnAddUsersToDeployment.Click

Dim iValAsInteger = 0

Dim SelectedCollectionAsString

SelectedCollection =""

If (lsbUsersAvail.Items).Count > 1Then

For iVal = 0To lsbUsersAvail.Items.Count - 1

If lsbUsersAvail.Items(iVal).Selected =TrueThen

SelectedCollection = SelectedCollection &"," & lsbUsersAvail.Items(iVal).Value

EndIf

Next

SelectedCollection = Mid(SelectedCollection, 2, Len(SelectedCollection))

Session.Item("SelectedCollectionSession") = SelectedCollection

SqlDataSource4.Update()

ltlUsersMessage.Text =String.Empty

'UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (@.selectedusersparam))

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")"

Else

ltlUsersMessage.Text ="Select users before adding to deployment. Hold Control for multiselect"

EndIf

EndSub

For some reason the query does not pass the parameters which are "21077679,22648722,22652940,21080617" into the query

I don't understand why.

hi,

could you debug your application, i want to know what actual query is being passed. you've for loop for some list, while debugging does it go inside this loop or not.

also if i am not wrong you've commented out following line

'SqlDataSource4.UpdateCommand = "UPDATE tblUserDetails SET DeploymentNameID = @.DeploymentNameIDparam WHERE (EmployeeNumber IN (" + SelectedCollection + ")".

seems like it is the cause as i dont see other line for setting updatecommand.

please check & let me know.

regards,

satish

|||

This works but does not use parameters:

ProtectedSub btnRemoveUsersFromDeployment_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnRemoveUsersFromDeployment.Click

Dim iVal2AsInteger = 0

Dim SelectedCollection2AsString

SelectedCollection2 =""

If (lstUsersToRemoveFromDeployment.Items).Count > 0Then

For iVal2 = 0To lstUsersToRemoveFromDeployment.Items.Count - 1

If lstUsersToRemoveFromDeployment.Items(iVal2).Selected =TrueThen

SelectedCollection2 = SelectedCollection2 &"," & lstUsersToRemoveFromDeployment.Items(iVal2).Value

EndIf

Next

SelectedCollection2 = Mid(SelectedCollection2, 2, Len(SelectedCollection2))

Session.Item("SelectedCollectionSession") = SelectedCollection2

SqlDataSourceInCurrentDeployment.UpdateCommand ="UPDATE tblUserDetails SET DeploymentNameID = null WHERE ((EmployeeNumber IN (" + SelectedCollection2 +")))"

SqlDataSourceInCurrentDeployment.Update()

Else

MsgBox("Please select user(s) first")

EndIf

EndSub

|||

hi,

i am not clear mate...in earlier post you were passing parameter for DeploymentNameID in update command now you've removed and used null!!!

i m confused, what is actual problem.

regards,

satish.

|||

I have used the same concept for two different situations.

In the last post I did I was removing the Deployment name ID created by the first update command.

cheers.

Ben.

List Filtering NOT Working

I have a Report where i want to show in a DataList only those values Filtered by one of the fields of my dataset. I want to show a Date value ONLY if another column value in that row in my dataset = "IN".

But nothing seems to happen here. No records are shown. It's like the condition is never true.

Steps:

1. I drag a DataList

2. I drag a DatasetField inside the DataList (automatically creates a textbox)

3. Right Click on the Datalist, Properties, then Filter Tab

I use the following statement on the Filter tab

=Cstr(Fields!TransactionType.Value) Like "IN"

or

=Cstr(Fields!TransactionType.Value) = "IN"

it doesnt work at all.

Does anyone knows what's going on?

Thanks

Jose

I've noticed that in filtering sometimes, in fact in most cases, I have to explicitly cast each side to the correct data type. So in your case

Expression: Operator: Value:

=Cstr(Fields!TransactionType.Value) = = Cstr("IN")

Hope this helps.

|||

PERFECTLY FINE

THANK YOU

List control - New Page

I'm a newbie to SRS and have an easy questsion. I have a list control with a
text box and want the values in that text box to appear on a new page. I've
tried all the page break options in the list properties but nothing will
work. I've also tried creating a details group on my text box and that does
not work either. Any help would be appreciated.Found the problem. Page was too wide, and it would not display correctly in
preview mode.
"jweesies" wrote:
> I'm a newbie to SRS and have an easy questsion. I have a list control with a
> text box and want the values in that text box to appear on a new page. I've
> tried all the page break options in the list properties but nothing will
> work. I've also tried creating a details group on my text box and that does
> not work either. Any help would be appreciated.

List as report parameter

Any hint how to build a report that prompts user to select multiple values
from a lookup table and uses the multiple values in WHRERE myfield IN
(<user-selected-list>) to select the data?
ThanksReporting Services does not provide this functionality ... supposedly coming
in a future release.
For now, you can just make the parameter a text box so the user can type in
a comma separated list ... then parse the parameter in the filter.
--
Shaun Beane, MCT, MCDBA, MCDST
http://dbageek.blogspot.com
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Basically you can't - multi value lists are not natively support in the
current version.
You can however roll it yourself, either by dynamically building the sql
string to use JobID In (@.somecommadelimitedlist) or if you have some SQL
skills you can create a function in SQL Server to take a comma separated
list and return a table for use in a query.
Check this out:
http://www.windowsitpro.com/Article/ArticleID/26244/26244.html?Ad=1
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Chapter 11 of the book "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" provides a work-round to have a multi-select pick list in the
parameter area.
It is quite well explained, starting out with a comma-separated textbox, but
involves some careful editing...
HTH