Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]..
.
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]
..
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfran
You probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parsing XML

I am importing an xml document into a table in SQL Server (SS) 2005.
It imports into a single column (XML datatype). After that I use the
script below to parse it out into different rows in another table.
select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Nu)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Do_IP)[1]',
xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
Ex_Station_Sy_Type)[1]',
from xmlimport
In the xml document there are more than one record. How do I loop
through the xml document? Or in other words change the [1] to [2]...
[3]...[4]...
I know it is easy, but for some reason I can't figure it out.
Anyone know?
Thanks!
chfranYou probably want to use the nodes() method. Can you post some sample XML
data?
Here's an idea if your data follows the format given:
CREATE TABLE #xmlimport (xmldata xml);
INSERT INTO #xmlimport (xmldata)
VALUES (N'<Lab_Exceptions>
<Lab_Exception_Data>
<A_Data>
<Ex_Station_Nu>100</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.1</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type A</Ex_Station_Sy_Type>
</A_Data>
<A_Data>
<Ex_Station_Nu>200</Ex_Station_Nu>
<Ex_Station_Do_IP>192.168.10.2</Ex_Station_Do_IP>
<Ex_Station_Sy_Type>Type B</Ex_Station_Sy_Type>
</A_Data>
</Lab_Exception_Data>
</Lab_Exceptions>');
SELECT c.value('Ex_Station_Nu[1]', 'int') AS Ex_Station_Nu,
c.value('Ex_Station_Do_IP[1]', 'varchar(100)') AS Ex_Station_Do_IP,
c.value('Ex_Station_Sy_Type[1]', 'varchar(100)') AS Ex_Station_Sy_Type
FROM #xmlimport x
CROSS APPLY x.xmldata.nodes('/Lab_Exceptions/Lab_Exception_Data/A_Data') AS
T(c);
DROP TABLE #xmlimport;
"chfran" <chfran@.gmail.com> wrote in message
news:9a50a933-c824-4d71-8864-8be76493a22e@.s19g2000prg.googlegroups.com...
>I am importing an xml document into a table in SQL Server (SS) 2005.
> It imports into a single column (XML datatype). After that I use the
> script below to parse it out into different rows in another table.
> select xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Nu)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Do_IP)[1]',
> xmldata.value('(/Lab_Exceptions/Lab_Exception_Data/A_Data/
> Ex_Station_Sy_Type)[1]',
> from xmlimport
> In the xml document there are more than one record. How do I loop
> through the xml document? Or in other words change the [1] to [2]...
> [3]...[4]...
> I know it is easy, but for some reason I can't figure it out.
> Anyone know?
> Thanks!
> chfran

Parent to Child mappings identification in a single query

Hi,

I want to retrive the output in a single SQL query.

I had two columns in a table; I used some values of column1 in column2.
I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.

example:

MasterTable

ParentRow ChildRow
--
A B
C D
E F
G C
H E
M G

Output required:

ParentRow ChildRow
--
A B
C D
E F
G D (C mapped tfrom D)
H F
M D (G mapped from C, and C mapped from D)

To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @.@.rowcount become zero.

I am looking for better performace and to get the output in a single query, instead of multiple passes.

Can any one suggest good tools and articles on performance tuning techniques?

Thanks
Sreekanth

Waht is your database? SQL Server 2000 or 2005?

|||

If you use SQL Server 2005,

Code Snippet

Create Table #data (

[ParentRow] char ,

[ChildRow] char

);

Insert Into #data Values('A','B');

Insert Into #data Values('C','D');

Insert Into #data Values('E','F');

Insert Into #data Values('G','C');

Insert Into #data Values('H','E');

Insert Into #data Values('M','G');

;WITH TREE

as

(

Select

[ParentRow],

[ChildRow],

[ChildRow] as [Alternate],

Cast('*' + '\' + '*' as varchar(max)) Path

from #Data

Union ALL

Select

data.[ParentRow],

data.[ChildRow],

Tree.[Alternate],

Cast(Path + '\' + '*' as varchar(max)) Path

from #Data as data

Join Tree On Data.[ChildRow] = Tree.[ParentRow]

)

,Tree2 as

(

Select

[ParentRow],

[ChildRow],

[Alternate],

Len(path) as PathLen,

Max(Len(path)) Over(Partition BY [ParentRow], [ChildRow]) MaxLen

from Tree

)

select

[ParentRow],

[Alternate]

from

Tree2

Where

PathLen=MaxLen

Drop table #data

|||

If you use SQL server 2000,

Code Snippet

create table datatable (

[parentrow] char ,

[childrow] char

);

insert into datatable values('A','B');

insert into datatable values('C','D');

insert into datatable values('E','F');

insert into datatable values('G','C');

insert into datatable values('H','E');

insert into datatable values('M','G');

go

create function findalternate(@.childvalue char)

returns char

as

begin

declare @.return as char;

select @.return = childrow from datatable where parentrow=@.childvalue;

if @.return is null

return @.childvalue

else

return dbo.findalternate(@.return)

return null;

end

go

select *, dbo.findalternate(childrow) from datatable

|||

I am using SQL 2005 Server.

Thank you for providing the T-SQL.

Parent Child hierarchy view

Is it possible with a datagrid to display a hierarchy view of a dataset
which is held in a single table that have an ID and Parent ID field
giving multiple levels of a hierarchy?
If it is possible how do we do it?
Regards
<<<Bryan>>Found a very useful url
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp

Parameters in Reporting Services...

Is it possible to control a parameter type as being single vs. multi-valued
based on the value selected in the previous parameter?
Thank you
Ramdaskeep it multi - but only show one value to select if previous parameter
selection so indicates.
"Ram" wrote:
> Is it possible to control a parameter type as being single vs. multi-valued
> based on the value selected in the previous parameter?
> Thank you
> Ramdas|||Hi,
Thanks for the tip. How would i show only one value based on the previous
parameter selection.
Thank you
Ramdas
"Jimbo" wrote:
> keep it multi - but only show one value to select if previous parameter
> selection so indicates.
>
>
> "Ram" wrote:
> > Is it possible to control a parameter type as being single vs. multi-valued
> > based on the value selected in the previous parameter?
> >
> > Thank you
> > Ramdas|||use a stored procedure to populate your select list - one of the parameters
for this stored procedure would indicate whether the return list will be
multiple records or a single record
this parameter would be set by user selection before being passed to the
stored procedure
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> Hi,
> Thanks for the tip. How would i show only one value based on the previous
> parameter selection.
> Thank you
> Ramdas
> "Jimbo" wrote:
>> keep it multi - but only show one value to select if previous parameter
>> selection so indicates.
>>
>>
>> "Ram" wrote:
>> > Is it possible to control a parameter type as being single vs.
>> > multi-valued
>> > based on the value selected in the previous parameter?
>> >
>> > Thank you
>> > Ramdas|||Is it possible to modify the XML code at runtime? I want to control the
report parameter properties multi-value setting of True/False during runtime
in the XML behind the RDL file. Set it to True if I want the parameters to be
multi-value or False for single-value. This is determined based on the value
selected in parameter one. Parameter one and two are City,State.
If City is selected in parameter one then I want Parameter two to be a
single-valued list, if State is chosen in Parameter One then I want the list
in Parameter two to be a multi-valued select list.
Any ideas or guidance would be appreciated.
"Jim" wrote:
> use a stored procedure to populate your select list - one of the parameters
> for this stored procedure would indicate whether the return list will be
> multiple records or a single record
> this parameter would be set by user selection before being passed to the
> stored procedure
>
>
>
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:60367FB5-4886-40A2-85F8-A08AF9A938E2@.microsoft.com...
> > Hi,
> > Thanks for the tip. How would i show only one value based on the previous
> > parameter selection.
> >
> > Thank you
> > Ramdas
> >
> > "Jimbo" wrote:
> >
> >> keep it multi - but only show one value to select if previous parameter
> >> selection so indicates.
> >>
> >>
> >>
> >>
> >> "Ram" wrote:
> >>
> >> > Is it possible to control a parameter type as being single vs.
> >> > multi-valued
> >> > based on the value selected in the previous parameter?
> >> >
> >> > Thank you
> >> > Ramdas
>
>

Parameters Changing Parameters

I have two parameters, Vendor and Status and I need to change the value of Status when the user changes the vendor from all to a single vendor and grey out the status parameter or just have it show all.

Any Guildence?.

Are you viewing your reports through the web browser, or through a viewer control in a program? I don't know if this is possible for doing it through the web browser. But if you're doing it through a program, you could write your own dialog for inputting the parameters, and then pass those along to the report viewer.|||I am working with the report through Visual Studio.|||

May be you need to read about "Cascading Parameters" to solve your problem.

Here is one link: http://msdn2.microsoft.com/en-us/library/aa337426.aspx

--Amde

|||I ened up finded that this could not be done. So I created a textbox that would come up when the users query would not return any results and tell them that they would need to select All to find out the info they were looking for.

Parameters & Sybase DB

I have a report that takes a single date parameter. My DataSource however,
could be either a MSSQL db or a Sybase db. Both of these are included as
shared datasources in my project (This is becase some clients of the report
may point to the MSSQL db while others will point to the Sybase db). However,
so far it seems that the rules for writing parameterized queries differ for
these 2 platforms i.e.
For MSSQL, i have:
where date > @.date,
but for Sybase, I had to say:
where date > ?
How can I write just one report so that when deployed, changing the
datasource from MSSQL to Sybase does not cause a problem?Have you tried doing a stored procedure and passing a the parameter in?...
That might work for both...(Although I am surprised that there is even the
difference you have discovered.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>I have a report that takes a single date parameter. My DataSource however,
> could be either a MSSQL db or a Sybase db. Both of these are included as
> shared datasources in my project (This is becase some clients of the
> report
> may point to the MSSQL db while others will point to the Sybase db).
> However,
> so far it seems that the rules for writing parameterized queries differ
> for
> these 2 platforms i.e.
> For MSSQL, i have:
> where date > @.date,
> but for Sybase, I had to say:
> where date > ?
> How can I write just one report so that when deployed, changing the
> datasource from MSSQL to Sybase does not cause a problem?|||One important principle of Reporting Services 2000 is to not "rewrite" the
dataset query. The statement is essentially sent directly to the data
provider. Some data providers do not support named parameters (like OleDb
data providers). Other data providers support named parameters, but they use
different syntax to mark parameters and may have various flavors for SQL
keywords.
E.g.:
Managed SQL Provider: select * from emp where name = @.Name
Managed Oracle Provider: select * from emp where name = :Name
For the Sybase provider, it looks like Aparna is using the Sybase OleDb
provider.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> Have you tried doing a stored procedure and passing a the parameter in?...
> That might work for both...(Although I am surprised that there is even the
> difference you have discovered.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>>I have a report that takes a single date parameter. My DataSource however,
>> could be either a MSSQL db or a Sybase db. Both of these are included as
>> shared datasources in my project (This is becase some clients of the
>> report
>> may point to the MSSQL db while others will point to the Sybase db).
>> However,
>> so far it seems that the rules for writing parameterized queries differ
>> for
>> these 2 platforms i.e.
>> For MSSQL, i have:
>> where date > @.date,
>> but for Sybase, I had to say:
>> where date > ?
>> How can I write just one report so that when deployed, changing the
>> datasource from MSSQL to Sybase does not cause a problem?
>|||Hi Robert,
I actually tired using both the Sybase ASE Ole DB provider, as well as the
'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
Problems'. Here are the steps he mentioned:
---
1. Create the ODBC Machine DSN and test that connection works
2. Pick provider Microsoft OLE DB Provider for ODBC drivers
3. Next
4. Use Data Source name and pick the DSN you created in 1
5. Enter username and password to use.
6. Pick initial catalog to use and click on test connection
7. Click OK
You should now be done. If you have a problem with username and password
after you are done double click on the data source and go to the credential
tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
---
In both cases, I tried running the parameterized query (using @.Date) in both
the Generic Query Designer as well the Visual Query Designer. In both cases,
I get the message "The Data Extension ODBC does not support named parameters.
Use unnamed parameters instead".
I did read about Oracle's conventions for using named parameters, and tried
searching for info. on using named parameters in Sybase, but to no avail. I
know Wayne mentioned using stored procedures, but I wanted to know if I could
use a simple query for both Sybase & Sql Server platforms. Any idea how named
parameters are used for Sybase? All in all, it seems to me that because
there are slight variations in syntax, etc between the various providers, it
may not be possible to write a parameterized query and then dynamically
change the data source provider once deployed (i.e. from Sql Server to Oracle
or Sybase)...Is that correct?
--Aparna.
"Robert Bruckner [MSFT]" wrote:
> One important principle of Reporting Services 2000 is to not "rewrite" the
> dataset query. The statement is essentially sent directly to the data
> provider. Some data providers do not support named parameters (like OleDb
> data providers). Other data providers support named parameters, but they use
> different syntax to mark parameters and may have various flavors for SQL
> keywords.
> E.g.:
> Managed SQL Provider: select * from emp where name = @.Name
> Managed Oracle Provider: select * from emp where name = :Name
> For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> provider.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > Have you tried doing a stored procedure and passing a the parameter in?...
> > That might work for both...(Although I am surprised that there is even the
> > difference you have discovered.)
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> >>I have a report that takes a single date parameter. My DataSource however,
> >> could be either a MSSQL db or a Sybase db. Both of these are included as
> >> shared datasources in my project (This is becase some clients of the
> >> report
> >> may point to the MSSQL db while others will point to the Sybase db).
> >> However,
> >> so far it seems that the rules for writing parameterized queries differ
> >> for
> >> these 2 platforms i.e.
> >> For MSSQL, i have:
> >> where date > @.date,
> >>
> >> but for Sybase, I had to say:
> >> where date > ?
> >>
> >> How can I write just one report so that when deployed, changing the
> >> datasource from MSSQL to Sybase does not cause a problem?
> >
> >
>
>|||For Sybase you cannot use named parameters (regardless of provider used).
For Sybase you put a ?
I have (unfortunately) been living in Sybase with RS so let me know if you
have any other difficulties. There are little quirks from time to time.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
> Hi Robert,
> I actually tired using both the Sybase ASE Ole DB provider, as well as the
> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
> Problems'. Here are the steps he mentioned:
> ---
> 1. Create the ODBC Machine DSN and test that connection works
> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
> 3. Next
> 4. Use Data Source name and pick the DSN you created in 1
> 5. Enter username and password to use.
> 6. Pick initial catalog to use and click on test connection
> 7. Click OK
> You should now be done. If you have a problem with username and password
> after you are done double click on the data source and go to the
credential
> tab.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ---
> In both cases, I tried running the parameterized query (using @.Date) in
both
> the Generic Query Designer as well the Visual Query Designer. In both
cases,
> I get the message "The Data Extension ODBC does not support named
parameters.
> Use unnamed parameters instead".
> I did read about Oracle's conventions for using named parameters, and
tried
> searching for info. on using named parameters in Sybase, but to no avail.
I
> know Wayne mentioned using stored procedures, but I wanted to know if I
could
> use a simple query for both Sybase & Sql Server platforms. Any idea how
named
> parameters are used for Sybase? All in all, it seems to me that because
> there are slight variations in syntax, etc between the various providers,
it
> may not be possible to write a parameterized query and then dynamically
> change the data source provider once deployed (i.e. from Sql Server to
Oracle
> or Sybase)...Is that correct?
> --Aparna.
> "Robert Bruckner [MSFT]" wrote:
> > One important principle of Reporting Services 2000 is to not "rewrite"
the
> > dataset query. The statement is essentially sent directly to the data
> > provider. Some data providers do not support named parameters (like
OleDb
> > data providers). Other data providers support named parameters, but they
use
> > different syntax to mark parameters and may have various flavors for SQL
> > keywords.
> > E.g.:
> > Managed SQL Provider: select * from emp where name = @.Name
> > Managed Oracle Provider: select * from emp where name = :Name
> >
> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
> > provider.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
> > > Have you tried doing a stored procedure and passing a the parameter
in?...
> > > That might work for both...(Although I am surprised that there is even
the
> > > difference you have discovered.)
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
> > >>I have a report that takes a single date parameter. My DataSource
however,
> > >> could be either a MSSQL db or a Sybase db. Both of these are included
as
> > >> shared datasources in my project (This is becase some clients of the
> > >> report
> > >> may point to the MSSQL db while others will point to the Sybase db).
> > >> However,
> > >> so far it seems that the rules for writing parameterized queries
differ
> > >> for
> > >> these 2 platforms i.e.
> > >> For MSSQL, i have:
> > >> where date > @.date,
> > >>
> > >> but for Sybase, I had to say:
> > >> where date > ?
> > >>
> > >> How can I write just one report so that when deployed, changing the
> > >> datasource from MSSQL to Sybase does not cause a problem?
> > >
> > >
> >
> >
> >|||I have a Sybase Anywhere db (version 8.x) that I have been using since RS
was released. For some reason my parameter (specified with a ?) is not
linking up with the selection entered in by the user prompt I have
associated with it. Any ideas? =)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
> For Sybase you cannot use named parameters (regardless of provider used).
> For Sybase you put a ?
> I have (unfortunately) been living in Sybase with RS so let me know if you
> have any other difficulties. There are little quirks from time to time.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
> both
>> the Generic Query Designer as well the Visual Query Designer. In both
> cases,
>> I get the message "The Data Extension ODBC does not support named
> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
> tried
>> searching for info. on using named parameters in Sybase, but to no avail.
> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various providers,
> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
> as
>> > >> shared datasources in my project (This is becase some clients of the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>|||Sometimes you have to hook them backup up. Go to the dataset, click on the
..., parameters tab. On the left put ? on the right pick the appropriate
report parameter. This should be done in the order that the ? appear in your
query.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:11hhcmvr55mpl81@.corp.supernews.com...
>I have a Sybase Anywhere db (version 8.x) that I have been using since RS
>was released. For some reason my parameter (specified with a ?) is not
>linking up with the selection entered in by the user prompt I have
>associated with it. Any ideas? =)
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OzPRC%23EEFHA.2876@.TK2MSFTNGP12.phx.gbl...
>> For Sybase you cannot use named parameters (regardless of provider used).
>> For Sybase you put a ?
>> I have (unfortunately) been living in Sybase with RS so let me know if
>> you
>> have any other difficulties. There are little quirks from time to time.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> news:CFA3F4CE-C695-4749-B8E2-A4155F9C25D0@.microsoft.com...
>> Hi Robert,
>> I actually tired using both the Sybase ASE Ole DB provider, as well as
>> the
>> 'Microsoft OLE DB for ODBC Drivers'. For the latter, I followed the
>> instructions posted by Bruce L.C in a thread labelled 'Sybase Connection
>> Problems'. Here are the steps he mentioned:
>> ---
>> 1. Create the ODBC Machine DSN and test that connection works
>> 2. Pick provider Microsoft OLE DB Provider for ODBC drivers
>> 3. Next
>> 4. Use Data Source name and pick the DSN you created in 1
>> 5. Enter username and password to use.
>> 6. Pick initial catalog to use and click on test connection
>> 7. Click OK
>> You should now be done. If you have a problem with username and password
>> after you are done double click on the data source and go to the
>> credential
>> tab.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> ---
>> In both cases, I tried running the parameterized query (using @.Date) in
>> both
>> the Generic Query Designer as well the Visual Query Designer. In both
>> cases,
>> I get the message "The Data Extension ODBC does not support named
>> parameters.
>> Use unnamed parameters instead".
>> I did read about Oracle's conventions for using named parameters, and
>> tried
>> searching for info. on using named parameters in Sybase, but to no
>> avail.
>> I
>> know Wayne mentioned using stored procedures, but I wanted to know if I
>> could
>> use a simple query for both Sybase & Sql Server platforms. Any idea how
>> named
>> parameters are used for Sybase? All in all, it seems to me that because
>> there are slight variations in syntax, etc between the various
>> providers,
>> it
>> may not be possible to write a parameterized query and then dynamically
>> change the data source provider once deployed (i.e. from Sql Server to
>> Oracle
>> or Sybase)...Is that correct?
>> --Aparna.
>> "Robert Bruckner [MSFT]" wrote:
>> > One important principle of Reporting Services 2000 is to not "rewrite"
>> the
>> > dataset query. The statement is essentially sent directly to the data
>> > provider. Some data providers do not support named parameters (like
>> OleDb
>> > data providers). Other data providers support named parameters, but
>> > they
>> use
>> > different syntax to mark parameters and may have various flavors for
>> > SQL
>> > keywords.
>> > E.g.:
>> > Managed SQL Provider: select * from emp where name = @.Name
>> > Managed Oracle Provider: select * from emp where name = :Name
>> >
>> > For the Sybase provider, it looks like Aparna is using the Sybase
>> > OleDb
>> > provider.
>> >
>> > --
>> > This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >
>> >
>> >
>> > "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> > news:Okyk1H4DFHA.208@.TK2MSFTNGP12.phx.gbl...
>> > > Have you tried doing a stored procedure and passing a the parameter
>> in?...
>> > > That might work for both...(Although I am surprised that there is
>> > > even
>> the
>> > > difference you have discovered.)
>> > >
>> > > --
>> > > Wayne Snyder, MCDBA, SQL Server MVP
>> > > Mariner, Charlotte, NC
>> > > www.mariner-usa.com
>> > > (Please respond only to the newsgroups.)
>> > >
>> > > I support the Professional Association of SQL Server (PASS) and it's
>> > > community of SQL Server professionals.
>> > > www.sqlpass.org
>> > >
>> > > "Aparna" <Aparna@.discussions.microsoft.com> wrote in message
>> > > news:C74EDB98-888E-4211-8902-871E927B524C@.microsoft.com...
>> > >>I have a report that takes a single date parameter. My DataSource
>> however,
>> > >> could be either a MSSQL db or a Sybase db. Both of these are
>> > >> included
>> as
>> > >> shared datasources in my project (This is becase some clients of
>> > >> the
>> > >> report
>> > >> may point to the MSSQL db while others will point to the Sybase
>> > >> db).
>> > >> However,
>> > >> so far it seems that the rules for writing parameterized queries
>> differ
>> > >> for
>> > >> these 2 platforms i.e.
>> > >> For MSSQL, i have:
>> > >> where date > @.date,
>> > >>
>> > >> but for Sybase, I had to say:
>> > >> where date > ?
>> > >>
>> > >> How can I write just one report so that when deployed, changing the
>> > >> datasource from MSSQL to Sybase does not cause a problem?
>> > >
>> > >
>> >
>> >
>> >
>>
>

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<

Parameters

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report work fine, but when I select more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

OK, do you want us to read the whole query ? As you are more involved in the logic of the query I would suggest you turning on the profile to see what is actually fired against the SQL Server database. But as from a first view you are using multivalue parameters like singlevalue and multivalue together in @.pzona = 0 and (@.pzona in @.Pzona whatever that means)

Using the profiler will help you to find your answer to that syntax problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I have a problem in SQL Reporting Services.

I have several multi-value parametersI

When I use one single value in parameter and running the report everything work fine, but when I chose more then one value in the parameter (With Multi Value ) it fails:

"An error occurred during local report precessing. Query execution failed for dataset "DataSet" incorrect syntax near ',' "

thanks

|||

You need to make sure that your SQL query in the DataSet uses the "IN" clause instead of "=".

For ex.

Select ColumnA,ColumnB,ColumnC from MyTable

where ColumnA in (@.Parameter)

|||

That error could mean that you are trying to use the IN syntax but the parameter values are of character/string type.

If so, please see this thread -- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1 -- it's a little involved, but you need to split the set of multiple parameters up and put them back together with string delimiters...

>L<