Parameterized Expression: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</
usedby></history>
<androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegr oups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4
</usedby></history>
>

parsing XML string in a text field

I am trying to build a query on a SQL2000 text field which stores XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure if
I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
1728725211 ABC 2005-03-25 8:09:56.700
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>nul
l</threshhold><usedby>user1
</usedby></history>
1728725211 ABC 2005-03-28 11:01:14.407
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastqu
antityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</
threshhold><usedby>user2</u
sedby></history>
1728725211 ABC 2005-03-28 11:46:12.723
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user1</
usedby></history>
1728725211 ABC 2005-03-28 11:46:35.273
<history><partnumber>abc</partnumber><type>2140461537</type><color>1</color>
<remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastq
uantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4<
/threshhold><usedby>user4</
usedby></history><androidsun@.yahoo.com> wrote in message
news:1112727090.902677.100370@.l41g2000cwc.googlegroups.com...
>I am trying to build a query on a SQL2000 text field which stores XML
> string. The query is like "select requestnumber from history where
> requestnumber is like '%re1%'". As you can see in the following sample
> records, the xml string has database structure and the requestnumber is
> a node of the XML. I wonder if it is possible to have SQL server parse
> this field and allow me to do the query. If not, any suggestion would
> be appreciated as to how to store XML data in SQL2000. I am not sure if
> I misused the SQL2000 XML feature correctly. So far I pass the raw
> query result to ADO and manipulate it in XMLDOM.
> The table is to capture history of changes in any record in my
> database. So I need to keep it simple so any record from any table can
> be stored in here. The structure of the table is like this:
> sysObjectNumber(int, not null)
> recordKeyValues(char(30), not null)
> archiveTime(datetime, not null)
> history(text, null)
> A sample record would be like the following:
> sysObjectNumber recordKeyValues arvhiveTime History
> =============== =============== =========== =======
> 1728725211 ABC 2005-03-25 8:09:56.700
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>
use
r1</usedby></history>
> 1728725211 ABC 2005-03-28 11:01:14.407
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></las
tquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>use
r2<
/usedby></history>
> 1728725211 ABC 2005-03-28 11:46:12.723
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er1
</usedby></history>
> 1728725211 ABC 2005-03-28 11:46:35.273
> <history><partnumber>abc</partnumber><type>2140461537</type><color>1</color><remai
n>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></la
stquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>us
er4
</usedby></history>
>

Wednesday, March 28, 2012

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

Parents of a SET of members

Hello,

I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])

--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]

The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Unfortunately this is not quite what I want and I should have been clearer in my initial question.
I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).

What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.

Do you have any other ideas please?

With continuing thanks
Mgale1

Parents of a SET of members

Hello,

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Do you have any other ideas please?

With continuing thanks
Mgale1

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 View without using Cursors

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'

I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?

If it's 2005, you can use CTE (Common Table Expreassions)

If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.

Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do

SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

But what I'm looking for is something closer to what this SQL generates.

USE Northwind
GO

SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.

The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?

Maybe something like

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works

Good Luck

...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

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 ...

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

(

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');

create function findalternate(@.childvalue char)

returns char

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

select *, dbo.findalternate(childrow) from datatable

|||

I am using SQL 2005 Server.

Thank you for providing the T-SQL.

Friday, March 23, 2012

Parent Child Query

I have a table and some same data.
entity_id | parent_entity_id | entity_name
-- -- --
4 0 Address Control
5 4 Address Line 1
6 4 Address Line 2
7 4 City
8 4 State
9 4 Zip Code
10 8 Maine
11 8 Massachusetts
12 8 New Hampshire
13 8 Rhode Island
14 8 Vermont
15 8 Connecticut
I'd like to pass in the entity_id, in this case 4 and get back all of the
children and grandchildren and I possibly down N, but would be happy with
grandchildren at this point as I dont know if deeper is needed.
If someone could point me to an article maybe or old post. I have looked
around some but see a lot of Oracle stuff.
Thanks"Brian" <brian@.nospam.com> wrote in message
news:uzKYuJqnFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I have a table and some same data.
> entity_id | parent_entity_id | entity_name
> -- -- --
> 4 0 Address Control
> 5 4 Address Line 1
> 6 4 Address Line 2
> 7 4 City
> 8 4 State
> 9 4 Zip Code
> 10 8 Maine
> 11 8 Massachusetts
> 12 8 New Hampshire
> 13 8 Rhode Island
> 14 8 Vermont
> 15 8 Connecticut
> I'd like to pass in the entity_id, in this case 4 and get back all of the
> children and grandchildren and I possibly down N, but would be happy with
> grandchildren at this point as I dont know if deeper is needed.
> If someone could point me to an article maybe or old post. I have looked
> around some but see a lot of Oracle stuff.
> Thanks
>
Get Celko's books on Trees and Hierarchies. Other items of interest, you
can google for nested set theory.
Rick Sawtell|||The following example includes a function that can be used to find
descendants and ascendants of an item in a hierarchy.
http://milambda.blogspot.com/2005/0...or-monkeys.html
A vital element is missing from your model - prevention of circular
references.
ML

Wednesday, March 21, 2012

Partitioned View performance

What is wrong with my partitioned view?
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.

Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any s

s and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any s

s and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>

parse query

Tuesday, March 20, 2012

Paremeter Control Greyed out

I have a balance sheet report with only one parameter, Month.
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>

Paramter posting back?

I have a report with four paramters.
A "Start Year" and "End Year" that are strings with values from a query, so
available valuse are 2003,2004. 2005, and 2006.
Two datetime for a "Start Transaction Date" and "End Transaction Date".
When I set the "Start Year" parameter, the report seems to post back and I
get the error:
The value provided for the report parameter 'TransStart' is not valid
for its type. (rsReportParameterTypeMismatch) Get Online Help
The paramter was empty, so of course it is not valid. How do I stop this
behavior? The paramters are not dependant on each other and do not use
dynamic SQL.
Any help would be appreciated.
Thanks Shannondid you set allow blank for the parameter that you allow it to be empty?

Friday, March 9, 2012

Parameters in subqueries ?

Can anyone tell me if this is a known bug? When using a parameter in a
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?[vbcol=seagreen]
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
=
> Windows
>
|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?

> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?

> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.
|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion

Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Why are you using Enterprise Manager for this?
it
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
>
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>
|||"news.microsoft.com" wrote:

> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion

> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation", and I'm just trying to create some tables from a script in Query Analyzer. I'm trying to create the tables in the pubs database. What's the deal
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:

> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion

> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables from a script in Query Analyzer. Can someone help me with this?
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:

> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>
|||I'm going to try this again. I'm having the same problem. I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables in the pubs database from a script. I'm using Que
ry Analyzer. Can someone help me with this.
"news.microsoft.com" wrote:

> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>

Friday, March 30, 2012

parsing XML string in a text field

parsing XML string in a text field

Wednesday, March 28, 2012

parse query

parse query

Parents of a SET of members

Parents of a SET of members

Monday, March 26, 2012

Parentheses in SQL Server 2005 Management Studio

Parent-Child View without using Cursors

Parent/child SQL query

Parent to Child mappings identification in a single query

Friday, March 23, 2012

Parent Child Query

Wednesday, March 21, 2012

Partitioned View performance

Partitioned view doesn't allow to query itself while insertion

parse query

Tuesday, March 20, 2012

Paremeter Control Greyed out

Paramter posting back?

Friday, March 9, 2012

Parameters in subqueries ?

Parameterized Expression

Blog Archive

About Me