DataTable query...

Hi...

In my appllication, i am having a DataTable... For that DataTable i have to write a query for a expression... This epression is more of mathematical expression type. Here is the sample expression-

(column1 < 1 && (column2 = 2 || column3 > 5) || column4 != 3)

How i can write a query for such a equation...? Is it possible to use logical & mathematical operator in the equation & to write a query for this type of equation... Is there is any good article on this...

Thanks in adavnce...

IamHuM

What exactly you need here. You can use the logical/math opertor as per your requirement.

Pls provide more clarity on your requirement.

|||

A good place to start is referring to Books Online, Topic 'Operators [Transact SQL]'.

Unfortunately, T-SQL does NOT have a full and rich set of mathematical operators. Depending upon your needs, you may be better served by exploring the capabilities of creating custom CLR functions (using VB.NET/C#.NET) and then having your SQL code use those CLR functions. A place to start with that is, again, Books Online, Topic: 'CLR User Defined Functions'.

As Mani indicated, if you post a more in-depth description of your requirements, we may be better able to guide you to the best resources.

|||

Code Snippet

select *

from
where Column1 < 1

and

(

(Column2 = 2 OR Column3 > 5)

OR

(Column4 <> 3)

)

Which is functionally equivalent to:

Code Snippet

select *

from <TableName>
where Column1 < 1

and

(

Column2 = 2

OR Column3 > 5

OR Column4 <> 3

)

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

I am moving this to the SQL Server Security forum.|||

From where you are connecting (trying) to this new database?

Is it from Reporting services or any other application?

|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

I am moving this to the SQL Server Security forum.|||

From where you are connecting (trying) to this new database?

Is it from Reporting services or any other application?

|||it is saved on my computer.|||You might check the permission for the user when trying to create this D-source connection, if the user do not have corresponding database privileges then it will fail.

Datasource-Microsoft Visual Studio

Hi

I am new to all SQL Related. I am trying to create a datasource to our old database we used in the company to write reports on the data. when I create a new data source and enter all required info, when testing the connection, i get a reply that I do not have the necessary permissions to use this object, although my account is specified as an administrator? can somebody please explain this to me.

Hi Hanel O,

Try posting this query to the MSDN SQL Server Data Access Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1&bcsi_scan_A9C07E6225287F19=z5cf+RkCipiHryeey+QUUQYAAAAmrYMB&bcsi_scan_filename=ShowForum.aspx

Also here is the MSDN SQL Server Forum main page, where you will find many SQL forums dedicated to different aspects of Microsoft SQL Server:
http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

Also try looking in SQL Server Books Online, which comes as part of the SQL Server install package.

Hope this helps,

Frank

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
..WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
Mike
You have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike
|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
selectconvert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike
|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegrou ps.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
.WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
MikeYou have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegroups.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>sql

Dataset Query Size limit (300 lines)

Hi,
I have a formatted Dataset query with size exceeding 300 lines (vertically). The dataset editor is not allowing me write it. Is this a limitation that the query size should be less than 300 lines or do we need have any way to increase this.
Formatting helps understand the query it in a legible format.
ThanksBy "dataset editor" do you mean the Dataset dialog, generic query designer
or graphical query designer? How long is the query in characters? What do
you mean by "not allowing me to write it"? Do you get an error message?
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> Hi,
> I have a formatted Dataset query with size exceeding 300 lines
(vertically). The dataset editor is not allowing me write it. Is this a
limitation that the query size should be less than 300 lines or do we need
have any way to increase this.
> Formatting helps understand the query it in a legible format.
> Thanks|||Apologise for this confusion.....I talked about the generic Query Designer [Report Design Window --> Data Tab -- > Place where the query is written]
I have a query as, say,
"select " & Parameters!col_name>Value & " as myValue from dual UNION ALL ...(almost 18k characters)"...
I wrote the above query as follows (for legibility)
" select " & Parameters!col_name>Value & " as myValue " & (press enter)
" from dual " & (press enter)
" UNION ALL " & (press enter)
... (exceeds 300 lines vertically now)
Here, i notice that only 300 lines come in the window and after that it does not paste or allow me manually write the query. Nothing happens when i write.
Kinldy let me know if it cannot work beyond this or a solution.
"Albert Yen [MSFT]" wrote:
> By "dataset editor" do you mean the Dataset dialog, generic query designer
> or graphical query designer? How long is the query in characters? What do
> you mean by "not allowing me to write it"? Do you get an error message?
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > Hi,
> >
> > I have a formatted Dataset query with size exceeding 300 lines
> (vertically). The dataset editor is not allowing me write it. Is this a
> limitation that the query size should be less than 300 lines or do we need
> have any way to increase this.
> >
> > Formatting helps understand the query it in a legible format.
> >
> > Thanks
>
>|||I am unable to reproduce your problem with a query with up to 600 lines and
30K characters. Could you post the text of your query? Although, it would
seem strange if the specific text made any difference.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> Apologise for this confusion.....I talked about the generic Query
Designer [Report Design Window --> Data Tab -- > Place where the query is
written]
> I have a query as, say,
> "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
...(almost 18k characters)"...
> I wrote the above query as follows (for legibility)
> " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> " from dual " & (press enter)
> " UNION ALL " & (press enter)
> ... (exceeds 300 lines vertically now)
> Here, i notice that only 300 lines come in the window and after that it
does not paste or allow me manually write the query. Nothing happens when i
write.
> Kinldy let me know if it cannot work beyond this or a solution.
> "Albert Yen [MSFT]" wrote:
> > By "dataset editor" do you mean the Dataset dialog, generic query
designer
> > or graphical query designer? How long is the query in characters? What
do
> > you mean by "not allowing me to write it"? Do you get an error message?
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > Hi,
> > >
> > > I have a formatted Dataset query with size exceeding 300 lines
> > (vertically). The dataset editor is not allowing me write it. Is this a
> > limitation that the query size should be less than 300 lines or do we
need
> > have any way to increase this.
> > >
> > > Formatting helps understand the query it in a legible format.
> > >
> > > Thanks
> >
> >
> >|||Well, this is really strange.
Please follow the process as,
format the query in an editor (textpad, in my case) with an enter character after every line. Copy this query which is beyond, say 400 lines, and paste it directly in datatset querystrign editor. Are you sure you get the complete query in the window. Try copying it back to the textpad editor and check for the number of lines.
My focus is primarily on having the copy and paste operation performed, as i did. Otherwise, if you can suggest any probable reason for this behaviour..
Thanks.
"Albert Yen [MSFT]" wrote:
> I am unable to reproduce your problem with a query with up to 600 lines and
> 30K characters. Could you post the text of your query? Although, it would
> seem strange if the specific text made any difference.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > Apologise for this confusion.....I talked about the generic Query
> Designer [Report Design Window --> Data Tab -- > Place where the query is
> written]
> > I have a query as, say,
> > "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
> ...(almost 18k characters)"...
> > I wrote the above query as follows (for legibility)
> > " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> > " from dual " & (press enter)
> > " UNION ALL " & (press enter)
> > ... (exceeds 300 lines vertically now)
> >
> > Here, i notice that only 300 lines come in the window and after that it
> does not paste or allow me manually write the query. Nothing happens when i
> write.
> > Kinldy let me know if it cannot work beyond this or a solution.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > By "dataset editor" do you mean the Dataset dialog, generic query
> designer
> > > or graphical query designer? How long is the query in characters? What
> do
> > > you mean by "not allowing me to write it"? Do you get an error message?
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a formatted Dataset query with size exceeding 300 lines
> > > (vertically). The dataset editor is not allowing me write it. Is this a
> > > limitation that the query size should be less than 300 lines or do we
> need
> > > have any way to increase this.
> > > >
> > > > Formatting helps understand the query it in a legible format.
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||I do see a limit of 32K characters in the textbox, after which I can't enter
any more text. I think the 32K character limit is imposed by the textbox
control, not us. That sounds like what you are seeing, except it's a limit
on the number of characters, not the number of lines. I'm not sure why you
have a problem after only 18K characters. At any rate, I don't have any
workarounds for this problem.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> Well, this is really strange.
> Please follow the process as,
> format the query in an editor (textpad, in my case) with an enter
character after every line. Copy this query which is beyond, say 400 lines,
and paste it directly in datatset querystrign editor. Are you sure you get
the complete query in the window. Try copying it back to the textpad editor
and check for the number of lines.
> My focus is primarily on having the copy and paste operation performed, as
i did. Otherwise, if you can suggest any probable reason for this
behaviour..
> Thanks.
> "Albert Yen [MSFT]" wrote:
> > I am unable to reproduce your problem with a query with up to 600 lines
and
> > 30K characters. Could you post the text of your query? Although, it
would
> > seem strange if the specific text made any difference.
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > Apologise for this confusion.....I talked about the generic Query
> > Designer [Report Design Window --> Data Tab -- > Place where the query
is
> > written]
> > > I have a query as, say,
> > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
ALL
> > ...(almost 18k characters)"...
> > > I wrote the above query as follows (for legibility)
> > > " select " & Parameters!col_name>Value & " as myValue " & (press
enter)
> > > " from dual " & (press enter)
> > > " UNION ALL " & (press enter)
> > > ... (exceeds 300 lines vertically now)
> > >
> > > Here, i notice that only 300 lines come in the window and after that
it
> > does not paste or allow me manually write the query. Nothing happens
when i
> > write.
> > > Kinldy let me know if it cannot work beyond this or a solution.
> > >
> > > "Albert Yen [MSFT]" wrote:
> > >
> > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > designer
> > > > or graphical query designer? How long is the query in characters?
What
> > do
> > > > you mean by "not allowing me to write it"? Do you get an error
message?
> > > >
> > > > --
> > > > Albert Yen
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > (vertically). The dataset editor is not allowing me write it. Is
this a
> > > > limitation that the query size should be less than 300 lines or do
we
> > need
> > > > have any way to increase this.
> > > > >
> > > > > Formatting helps understand the query it in a legible format.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||Is it practical for you to write the query as a stored procedure and call it
from the report?
"Albert Yen [MSFT]" wrote:
> I do see a limit of 32K characters in the textbox, after which I can't enter
> any more text. I think the 32K character limit is imposed by the textbox
> control, not us. That sounds like what you are seeing, except it's a limit
> on the number of characters, not the number of lines. I'm not sure why you
> have a problem after only 18K characters. At any rate, I don't have any
> workarounds for this problem.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> > Well, this is really strange.
> > Please follow the process as,
> > format the query in an editor (textpad, in my case) with an enter
> character after every line. Copy this query which is beyond, say 400 lines,
> and paste it directly in datatset querystrign editor. Are you sure you get
> the complete query in the window. Try copying it back to the textpad editor
> and check for the number of lines.
> >
> > My focus is primarily on having the copy and paste operation performed, as
> i did. Otherwise, if you can suggest any probable reason for this
> behaviour..
> >
> > Thanks.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > I am unable to reproduce your problem with a query with up to 600 lines
> and
> > > 30K characters. Could you post the text of your query? Although, it
> would
> > > seem strange if the specific text made any difference.
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > > Apologise for this confusion.....I talked about the generic Query
> > > Designer [Report Design Window --> Data Tab -- > Place where the query
> is
> > > written]
> > > > I have a query as, say,
> > > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
> ALL
> > > ...(almost 18k characters)"...
> > > > I wrote the above query as follows (for legibility)
> > > > " select " & Parameters!col_name>Value & " as myValue " & (press
> enter)
> > > > " from dual " & (press enter)
> > > > " UNION ALL " & (press enter)
> > > > ... (exceeds 300 lines vertically now)
> > > >
> > > > Here, i notice that only 300 lines come in the window and after that
> it
> > > does not paste or allow me manually write the query. Nothing happens
> when i
> > > write.
> > > > Kinldy let me know if it cannot work beyond this or a solution.
> > > >
> > > > "Albert Yen [MSFT]" wrote:
> > > >
> > > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > > designer
> > > > > or graphical query designer? How long is the query in characters?
> What
> > > do
> > > > > you mean by "not allowing me to write it"? Do you get an error
> message?
> > > > >
> > > > > --
> > > > > Albert Yen
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > >
> > > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > > (vertically). The dataset editor is not allowing me write it. Is
> this a
> > > > > limitation that the query size should be less than 300 lines or do
> we
> > > need
> > > > > have any way to increase this.
> > > > > >
> > > > > > Formatting helps understand the query it in a legible format.
> > > > > >
> > > > > > Thanks
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

dataset connection string defined by a parameter?

Is there any way to dynamically set the connection string for a dataset
based on the value of a parameter?
Would I have to write a custom data extension to accomplish this?
thanks, AndrewHello,
Datasource connection string is not part of your report. It is provided by
ReportServer.
So, datasource is not aware about parameter's reports.
Jerome BERTHAUD MCSD, MCT
http://www.winsight.fr
"Andrew" <nospam@.nospam.com> wrote in message
news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>|||Hi Jerome,
Thanks for the info. So basically, it would be impossible to set the
connection string from within the designer based on a parameter.
However, wouldn't it still be possible to get hold of the parameters
collection within a custom data extension and use that to determine the
connection string prior to actually querying the datasource?
Thanks, Andrew
"Jerome BERTHAUD" <jerome.berthaud@.winsight.fr> wrote in message
news:uigOlEfWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Datasource connection string is not part of your report. It is provided by
> ReportServer.
> So, datasource is not aware about parameter's reports.
> Jerome BERTHAUD MCSD, MCT
> http://www.winsight.fr
> "Andrew" <nospam@.nospam.com> wrote in message
> news:#m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Is there any way to dynamically set the connection string for a dataset
> > based on the value of a parameter?
> >
> > Would I have to write a custom data extension to accomplish this?
> >
> > thanks, Andrew
> >
> >
>|||I have released a DPE that achiveves this. It can be downloaded at
http://workspaces.gotdotnet.com/appworld
Regards
Toby
"Andrew" <nospam@.nospam.com> wrote in message
news:%23m8p1veWEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Is there any way to dynamically set the connection string for a dataset
> based on the value of a parameter?
> Would I have to write a custom data extension to accomplish this?
> thanks, Andrew
>

dataflow to excel - Convert numbers stored as text to numbers Excel Cell Error

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:\@.; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.

So close.. Thanks for any help or information.

You received some sugestion on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1392693&SiteID=1

Opening new threads for same problem makes more dificult to help you.

DATABASEPROPERYEX is not a recognised function name

I am trying to write a report in reporting services 2000 which access a sql server 7 database. It all workes fine with the connection and creating a dataset but I cannot seem to get the results displayed in the preview section. I get the following error:

"An error occured during report processing query execution failed for dataset XXXX DATABASEPROPERYEX is not a recognised function name "

Any help greatly appreciated

Your are running RS 2000 RTM - you have two options:

* install RS 2000 SP1 or SP2 on both the report server and the report designer machines. The service packs contain the fix.

* Alternatively, in report designer go to the "Data Options" tab of the "Dataset"
dialog. On the Data Options tab you will see that all settings contain "Auto" (and report server would therefore try to auto-detect the collation settings from the database server). Replace the Auto-settings with the following settings (e.g. if your SQL 7.0
database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true

-- Robert