Parameterized Expression: queries

Showing posts with label queries. Show all posts

Monday, March 26, 2012

Parentheses in SQL Server 2005 Management Studio

When writing queries in the query editor of SQL Server 2005 Management Studio, I would like the matching parentheses to be highlighted whenever I have my cursor over an opening parentheses. Obviously this functionality is there. If you delete and retype and existing opening parentheses, the matching closing paren will be highlighted. How do I turn this on all the time? Thanks,

Hi Brian, Sounds like this is a possible bug. Check out the product feedback center you can search to see if someone else has submitted the same problem, if so you can vote on it, if not you can submit it. You can access the product feedback center by going to http://lab.msdn.microsoft.com/productfeedback/Default.aspx.

Cheers,
Dan

|||I filed a bug report that can be found at:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=175f05ad-830d-4070-bc4e-1c9a4ee23ad4

Parent/child SQL query

Hello, I woud like to find a resource (book, web site, etc.) that
demonstrates writing queries related to parent/child relationships. In
particular I have a table that contains an unknown number of parents/childre
n
(and grand-children, great grand children, etc). I would like to create a
pivot table so that all the parents become columns and each row represents a
child (grand child, great grand child, etc.) for that parent.
Thank you,
RicOne of them is "Trees and Hierarchies" by Joe Celko. I'm sure he's just
writing a response himself. ;)
However, what you've specified in your post is best handled on the client,
as it's obviously just for presentation purposes (it breaks nromalization).
ML
http://milambda.blogspot.com/|||>> I woud like to find a resource (book, web site, etc.) that demonstrates w
riting queries related to parent/child relationships.<<
Buy a copies of TREES & HIERARCHIES IN SQL, of course! Steral the code
in the book.|||Check out the stuff Izik Ben Gan has done on trees and hierarchies.
Don't buy celko's book, its based around standard sql rather than Microsoft
SQL Server so you'll find it hard to implement, also, a lot of his methods
are slow, dont scale and out of date.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:CB71D7E5-0534-404B-8787-1F2F527F0D0A@.microsoft.com...
> Hello, I woud like to find a resource (book, web site, etc.) that
> demonstrates writing queries related to parent/child relationships. In
> particular I have a table that contains an unknown number of
> parents/children
> (and grand-children, great grand children, etc). I would like to create a
> pivot table so that all the parents become columns and each row represents
> a
> child (grand child, great grand child, etc.) for that parent.
> Thank you,
> Ric|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OEbv0ykIGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Check out the stuff Izik Ben Gan has done on trees and hierarchies.
Yes, but why isn't there dedicated sql for this?
Even if they had to use xml? :)
I know, it's a high priority for a future release......Please:):(

> Don't buy celko's book, its based around standard sql rather than
> Microsoft SQL Server so you'll find it hard to implement, also, a lot of
> his methods are slow, dont scale and out of date.
Yes, but why is it so popular on this ng?
Required reading according to many of the experts!
The twilight is blinding...:)
www.rac4sql.net|||>> Yes, but why isn't there dedicated sql for this? Even if they had to use
xml? :) <<
Recursive CTE expressions are now a part of Standard SQL. I like CTEs
but I do not like the recursirve version -- it seldom used so it
destorts the engine and it is a screaming XXXXX to optimize.
You silly boy! Because this book is the brilliant work of a known SQL
genius, who is kind to animals, God's gift to strippers as well as
undergrad math students, and so much nicer in person than on
Newsgroups!! Soon to be listed on a webite for sainthood ..
Seriously, if you cannot translate Standard SQL into any product
dialect, then you are a truly bad SQL programmer. This is like saying
"I only speak Hillbilly English and cannot leave my ghetto!"
"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898|||> ...God's gift to strippers as well as
> undergrad math students...
Geez! LOL :)

> Soon to be listed on a webite for sainthood...
Be sure to post the link. ;)
ML
http://milambda.blogspot.com/|||>> > Soon to be listed on a website for sainthood...Be sure to post the link
. ;) <<
Well, first, I have to get the domain registered ...

Tuesday, March 20, 2012

Parellism not working

We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
when we run queries against the newer hardware running SQL2000 we
notice that the execution plan shows no parallelism taking place. The
SQL2000 config. has 2 processors and both procs are enabled in the
Processor tab.(use all available processors is selected). I've
upgraded the install to SQL2000 SP3a but there is no change. Does
anyone know what may be going on here?

Thanks,
GCMost queries will not benefit from parallelism. The optimizer will
choose a parallel plan only if it makes sense for a particular query and
the server is not overtaxed.

In many cases, a parallel plan is an indication that you need to do some
index tuning.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311180934.6f7b8540@.posting.google.c om...
> We have migrated databases from SQL7 to SQL2000 Standard Edition. Now,
> when we run queries against the newer hardware running SQL2000 we
> notice that the execution plan shows no parallelism taking place. The
> SQL2000 config. has 2 processors and both procs are enabled in the
> Processor tab.(use all available processors is selected). I've
> upgraded the install to SQL2000 SP3a but there is no change. Does
> anyone know what may be going on here?
> Thanks,
> GC

Parametrized Report with Analysis Services didn't work

Y made 2 report with parametrized MDX queries and they only work with the default values. But when I select some values from the combos with legal values the report is empty.
The weird thing is when I test the report in visual studio it works really fine. But when I go from http://somemachine/reports it fails.
Can anybody try this?
The MDX querie is ok. I have a lot of experience with MDX in SQL 2000.
It says something like this.

WITH
MEMBER [Measures].[Caida] AS 'IIF([Measures].[Variacion]<-0.5, [Measures].[Variacion], NULL)'
SELECT
NON EMPTY { [Measures].[Caida]} ON COLUMNS,
NON EMPTY { STRTOMEMBER(@.Grupo).CHILDREN } ON ROWS
FROM Ventas
WHERE ( {STRTOMEMBER(@.Mes)} )

I made others easier queries with the same behavior of the empty report.
Can anybody help me or know what happend?
Thanks in advanceCan you post an RDL / query that runs against the AdventureWorks sample cube and exposes the same issue?
Additional questions:
* Which build of BI Development Studio are you running?
* Which build of RS 2005 is running on the report server?
* Which build of AS 2005 is running on the AS server?

-- Robert|||

I'm not using adventure works. I'm using all of my own. But if you see the query is really simple (no tricks). Can you help me?

<?xml version="1.0" encoding="utf-8"?>

<rd:DataSourceID>b60468c5-c60b-47e2-942d-eb4686e92533</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Fecha].[Standard].[Anio].&[15]</Value>

</Values>

</DefaultValue>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Black</Bottom>

</BorderColor>

</BorderWidth>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

<Value>Caida de las Ventas respecto a¤o anterior</Value>

</Textbox>

<GroupExpression>=Fields!Anio.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=Fields!Anio.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<rd:DefaultName>Anio</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Solid</Bottom>

</BorderStyle>

</Style>

<Value>="A¤o: "+Fields!Anio.Value</Value>

</Textbox>

</MatrixColumn>

</MatrixColumns>

<GroupExpression>=Fields!Grupo.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=Fields!Grupo.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<rd:DefaultName>Grupo</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

</Style>

<Value>=Fields!Grupo.Value</Value>

</Textbox>

</ReportItems>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<GroupExpression>=Fields!Mes.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<rd:DefaultName>Mes</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

<Value>=Fields!Mes.Value</Value>

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

</Style>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

<rd:DefaultName>Caida_mas_de_25_</rd:DefaultName>

<ReportName>Caida Ventas por Producto</ReportName>

<Value>=Fields!Mes.Value</Value>

</Parameter>

<Value>=Fields!Grupo.Value</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<ToolTip>Ver detalle caida de las ventas para este mes y este grupo</ToolTip>

<Style>

<TextDecoration>Underline</TextDecoration>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!Caida_mas_de_25_.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</Corner>

</Matrix>

</ReportItems>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

</Body>

<rd:ReportID>bc8342a4-de2c-4d93-ab3c-f257505a4aa9</rd:ReportID>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<CommandText>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</CommandText>

<Value>=Parameters!Anio.Value</Value>

</QueryParameter>

</QueryParameters>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Mes</LevelName><UniqueName>[Fecha].[Standard].[Mes]</UniqueName></ID><ItemCaption>Mes</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Producto</DimensionName><HierarchyName>Grupo</HierarchyName><HierarchyUniqueName>[Producto].[Grupo]</HierarchyUniqueName><LevelName>Grupo</LevelName><UniqueName>[Producto].[Grupo].[Grupo]</UniqueName></ID><ItemCaption>Grupo</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Caida mas de 25%</MeasureName><UniqueName>[Measures].[Caida mas de 25%]</UniqueName></ID><ItemCaption>Caida mas de 25%</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</Statement><ParameterDefinitions><ParameterDefinition><Name>Anio</Name><DefaultValues><DefaultValue>[Fecha].[Standard].[Anio].&[15]</DefaultValue></DefaultValues><Caption>Anio</Caption><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Fecha].[Standard].ALLMEMBERS ON ROWS FROM [Ventas]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</CommandText>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterCaption</MeasureName><UniqueName>[Measures].[ParameterCaption]</UniqueName></ID><ItemCaption>ParameterCaption</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterValue</MeasureName><UniqueName>[Measures].[ParameterValue]</UniqueName></ID><ItemCaption>ParameterValue</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterLevel</MeasureName><UniqueName>[Measures].[ParameterLevel]</UniqueName></ID><ItemCaption>ParameterLevel</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

</DataSets>

</Report>

|||After 2 days of research and kicking my desktop.
I found finnally that to build parameters you must not use UNIQUENAME. Instead use MEMBER [Measures].[ParameterValue] AS ' "[DimensionName].["+[DimensionName].CURRENTMEMBER.NAME+"]" '
It's works fine.
I think there is a bug using STRTOMEMBER(@.parameter) when @.parameter is in the UNIQUENAME form.
I hope it helps you too.|||I edit the previous post where i said that you can only put dimensions on columns. That's wrong, i'm using dimension in columns and rows and works really fine.

Friday, March 9, 2012

Parameters in named queries

Hi,

I try to build a named query that would either take a parameter or run a procedure to get a value.

The idea is to have a named query used by the cube partition. The named query must limit the lower boud time item according to a complex logic build in a stored procedure.

First I am not sure if it will work even if I can get the named query to behave this way, however I would rather avoid to alter the view behind the cube.

If possible, how can I create a parameter in a named query, something like this in pseudo-code

? = (EXEC up_FirstCrawl_Qtr)
SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = ?)

SELECT *
FROM dbo.V_ST_Crawl
WHERE (RSD_QRTR = (EXEC up_FirstCrawl_Qtr) )

Thanks,

Philippe

Not to answer your question directly, but I think you would find this very useful.

Project REAL just released entire set of scripts and packages and whole lot of other stuff you can use to create your data warehouse and Analysis Services cubes.

I think this is great material and you should be able to find answers to many of your questions there:

Here is the link: http://www.microsoft.com/downloads/thankyou.aspx?familyId=b61a37b6-5852-4018-bba9-795a34123ed0&displayLang=en&oRef=

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Woowh, Great.
I always wanted to get more from RealProject than abstracts.
Looks like the real meat is there now, 240MB or so of it.

I am sure going to spend a week-end my wife will remember :-)

In the meantime and to cope with management deadline, I will create another view. I am under the gun.

I always wanted to avoid providing things that work now but require more maintenance, however it is not always possible. problem is that management sees value only in brand new stuff never done before.
No budget nor time is allowed to re-create "working" existing legacy stuff in a much better way, but, they complain when the stuff is down for maintenance... The Chicken or the Egg? No both please.

Have a nice week-end

Philippe

Parameters in data flow task with Oracle database source

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

pshotts wrote:

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
Am I missing an obvious workaround?

below is workaround, but i don't know how "obvious" it is:

Use property expressions. The SQL statement you use to query the Oracle source can be set by a variable, and that variable can be composed using a property expression such as: "select * from Orders where OrderID > " + @.LastOrderID. It's a parameterized query - and a very flexible one - in all but name.

|||Thanks Duane, but that does not get around the 4000 character limit on variable and expression length|||

Can you use a combination of query strings (in SSIS) and views in Oracle to reduce the amount of work you are doing in the one string?

Another alternative might be to execute a stored procedure that builds a temporary table (passing the parameters to the procedure), then read from the temporary table.

A third alternative would be to pull back the various discrete data sources from Oracle and handle joining/merging within SSIS. Can't say I'd recommend this one though. Better to just pull across the data you need from the remote source.

|||

Another option is to use a script source in which you concatenate the query string and call Oracle using OLEDB or ADO.Net in the script.

Donald

|||

Thanks Donald

That sounds like a good option. I understand that you mean we should use a Script Component as the Source in the Data Flow. The script will make the connection and build and run the required query.

Nice!

|||

That's correct.

You can still use a connection manager with your script component, to take advantage of that feature, but even that is not essential. although recommended.

Donald

Monday, February 20, 2012

Parameterized queries with RDA

Hello..

Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?

Thanks in advance.

Best option is to use a stored procedure on the server and use RDA to execute it.

Darren

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>
>|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.
Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:

> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>
>
|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clause
> values were hard coded ?
> Thanks.
>

Parameterized queries taking more time

Do parameterized queries take up more CPU resources than if the where clause
values were hard coded ?
Thanks.Chakravarthy,
can you tell us where are you testing this and how?
AMB
"Chakravarthy" wrote:

> Do parameterized queries take up more CPU resources than if the where clau
se
> values were hard coded ?
> Thanks.
>
>|||I'm sure they take up a very, very, very small amount more than a
hard-coded query. The query PLAN that's used is far and away the most
important thing to consider, though. If the plans are identical, you
will have queries which, under the same conditions, will perform
identically.
Chakravarthy wrote:
> Do parameterized queries take up more CPU resources than if the where clau
se
> values were hard coded ?
> Thanks.
>

Parameterized queries - works in Access but not SQLS2k?

I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.

This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.

Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?

Hoping you can help
Alex

PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;

SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp

FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID

WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));(tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
Mdaaa...
tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
???|||Thanks Buser - works fine - much obliged to you!

Regards
Alex

Parameterized Queries

Just getting started using SSce and having a few problems

What I want to do is something like this...

Dim Code As Integer

Dim Description As String = txtDescription.Text.Trim

Dim conn As SqlCeConnection = ConnectToLocalDatabase()

Dim ssql As New System.Text.StringBuilder

ssql.AppendLine("INSERT INTO T_Titles (Description)")

ssql.AppendLine("VALUES(@.Description)")

ssql.AppendLine("SELECT @.Code = @.@.IDENTITY")

Dim cmd As New SqlCeCommand(ssql.ToString, conn)

Dim sqlCode As New SqlCeParameter("@.Code", 0)

sqlCode.Direction = ParameterDirection.InputOutput

cmd.Parameters.Add(sqlCode)

cmd.Parameters.Add(New SqlCeParameter("@.Description", Description))

cmd.ExecuteNonQuery()

Code = CInt(sqlCode.Value)

**********************************************************************

The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.

I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.

Thanks

Sadly, SQL Server mobile does not support batch queries. Queries must be a single SQL statement. Stored prcedures are therefore not supported either. ExecuteNonQuery returns the number of rows affected for selects. For more information see:

http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.aspx

|||

Thanks ErikEJ

That explains everything. It also solves my second problem with output parameters. If you cant have multiple queries then Output Parameters wouldnt be very useful either.

Thank you again.

David

parameterized pass-through queries from Access front-end?

Is there any easy way to pass (dynamically) parameters to pass-through
queries,
when working with MS Access as front-end for SQL Server ?

Thanks."Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d3ghk9$h7g$1@.ss405.t-com.hr...
> Is there any easy way to pass (dynamically) parameters to pass-through
> queries,
> when working with MS Access as front-end for SQL Server ?
> Thanks.

Your question seems to be about handling parameters in the front end, so you
might get a better answer in an Access group.

Simon

Parameterized MDX queries using AdomdParameter

Are there any good examples out there of performing parameterized MDX queries, using the new AdomdParameter class in 9.0? The only sample I've found so far involves querying KPIs, but I just want to query standard measures and dimensions.

In our experimentation, we're trying to use a parameter for the measure name in a query, eg.
SELECT @.measure on columns from [CubeName]

Running that command throws an error:
The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used

However, it works when I change the command to:
SELECT strtomember(@.measure) on columns from [CubeName]

Is wrapping all the parameter values in strtomember required? That seems a bit onerous.

Also, are there restrictions about where parameters can be used? Can you use them for the cube name? Slicer? Can you specify sets? The documentation around the usage of AdomdParameter is very slim.

To answer one of your questions;

You should simply change the query to:

SELECT {@.measure} on columns from [CubeName]

As for the rest. I dont think there are any limitations. You can simply try the combinations you need.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Doh! Sorry, I pasted in the wrong query (I've been playing around with different options). I actually did have the braces in my query, and it still failed. Here's some test code that I wrote against the AW database:

AdomdConnection connection = new AdomdConnection();
connection.ConnectionString = "Data Source=localhost;Initial Catalog=Adventure Works DW Standard Edition";
connection.Open();
try
{
AdomdCommand command = new AdomdCommand();
command.Connection = connection;
command.CommandText = "SELECT {@.measure} on columns from [Adventure Works]";
AdomdParameter param = command.CreateParameter();
param.ParameterName = "measure";

param.Value = "[Measures].[Internet Order Count]";

command.Parameters.Add(param);
command.Execute();

}
finally
{
connection.Close();
}

it throws the exception:
Query (1, 8) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

When I change it to:
"SELECT {strtomember(@.measure)} ..."

it works.

Am I missing something obvious here?
|||

hello Kevin,

i think what you observe is as expected. in the example above query has a string parameter, and as such if there is no strtomember call, the error is raised that a string expression was used.

Whether you need to call strtomember or other function depends on what the parameter means and where it is used in query. For example if you had a query doing filtering on member name containing or starting with a parameterized string - then you'd just use @.param. If your parameter contained a set, you'd probably use strtoset function. If you had an integer parameter to use in some comparison for example, then again you'd probably use it as is.

hope this helps.

Monday, March 26, 2012

Parentheses in SQL Server 2005 Management Studio

Parent/child SQL query

Tuesday, March 20, 2012

Parellism not working

Parametrized Report with Analysis Services didn't work

Friday, March 9, 2012

Parameters in named queries

Parameters in data flow task with Oracle database source

Monday, February 20, 2012

Parameterized queries with RDA

Parameterized queries taking more time

Parameterized queries taking more time

Parameterized queries taking more time

Parameterized queries - works in Access but not SQLS2k?

Parameterized Queries

parameterized pass-through queries from Access front-end?

Parameterized MDX queries using AdomdParameter

Parameterized Expression

Blog Archive

About Me