better method for DTS: code

Showing posts with label code. Show all posts

Friday, March 30, 2012

Is this T-SQL dangerous/not reliable

We are trying to create a string of column names for a
given table. (SQL 2000, SP3a--W2K Server, SP3)
The included code here works well so far, but I remember
reading in the past that the optimizer can sometimes break
this approach to creating strings.
If all column names are not null and the total len(string)
does not exceed varchar(8000), is this construct safe? If
not, why?
TIA, -- Brian
declare @.FldStr varchar(8000)
select @.FldStr = ''
select @.FldStr = @.FldStr + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @.TableName
order by ORDINAL_POSITIONThis behavior isn't documented and also isn't supported. I believe someone
posted an example not too long ago that showed this syntax breaking, but
can't seem to find it on a quick search of google.
Why not return the set of column names to your application, and have the
application assemble them into a string?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
>
>
>|||Thanks, Aaron.
I'm taking it further and doing joins dynamically with
sp_executesql, thus would like to keep it on the backend.
Would sending the field names to a #temp table with an
identity field be a better to go, looping through 1 to n
records to build the string of field names?
I'll also try to find the post you mentioned. I'm very
interested in the behind the scenes stuff that would cause
this to break. If you find it at a later point, I'd
greatly appreciate it if you could forward it to me.
(brianglinebaugh@.yahoo.com)
Thanks very much for your time, -- Brian
>--Original Message--
>This behavior isn't documented and also isn't supported.
I believe someone
>posted an example not too long ago that showed this
syntax breaking, but
>can't seem to find it on a quick search of google.
>Why not return the set of column names to your
application, and have the
>application assemble them into a string?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Brian" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c401c3b076$8d083260$a001280a@.phx.gbl...
>> We are trying to create a string of column names for a
>> given table. (SQL 2000, SP3a--W2K Server, SP3)
>> The included code here works well so far, but I remember
>> reading in the past that the optimizer can sometimes
break
>> this approach to creating strings.
>> If all column names are not null and the total len
(string)
>> does not exceed varchar(8000), is this construct safe?
If
>> not, why?
>> TIA, -- Brian
>> declare @.FldStr varchar(8000)
>> select @.FldStr = ''
>> select @.FldStr = @.FldStr + COLUMN_NAME
>> from INFORMATION_SCHEMA.COLUMNS
>> where TABLE_NAME = @.TableName
>> order by ORDINAL_POSITION
>>
>>
>>
>
>.
>|||"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> We are trying to create a string of column names for a
> given table. (SQL 2000, SP3a--W2K Server, SP3)
> The included code here works well so far, but I remember
> reading in the past that the optimizer can sometimes break
> this approach to creating strings.
> If all column names are not null and the total len(string)
> does not exceed varchar(8000), is this construct safe? If
> not, why?
> TIA, -- Brian
> declare @.FldStr varchar(8000)
> select @.FldStr = ''
> select @.FldStr = @.FldStr + COLUMN_NAME
> from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = @.TableName
> order by ORDINAL_POSITION
>
How about
set @.FldStr = '*'
?
David|||I don't know of a KB that states this but it will fail most of the time in
anything other than a simple select with no order by, join etc. You do not
want to put that code into your production env. Create a cursor and di it
that way if you must have it in a particular order.
--
Andrew J. Kelly
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2c01c3b07f$eef7fab0$a601280a@.phx.gbl...
> Thanks, Aaron.
> I'm taking it further and doing joins dynamically with
> sp_executesql, thus would like to keep it on the backend.
> Would sending the field names to a #temp table with an
> identity field be a better to go, looping through 1 to n
> records to build the string of field names?
> I'll also try to find the post you mentioned. I'm very
> interested in the behind the scenes stuff that would cause
> this to break. If you find it at a later point, I'd
> greatly appreciate it if you could forward it to me.
> (brianglinebaugh@.yahoo.com)
> Thanks very much for your time, -- Brian
>
>
>
> >--Original Message--
> >This behavior isn't documented and also isn't supported.
> I believe someone
> >posted an example not too long ago that showed this
> syntax breaking, but
> >can't seem to find it on a quick search of google.
> >
> >Why not return the set of column names to your
> application, and have the
> >application assemble them into a string?
> >
> >--
> >Aaron Bertrand
> >SQL Server MVP
> >http://www.aspfaq.com/
> >
> >
> >
> >
> >"Brian" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c401c3b076$8d083260$a001280a@.phx.gbl...
> >>
> >> We are trying to create a string of column names for a
> >> given table. (SQL 2000, SP3a--W2K Server, SP3)
> >>
> >> The included code here works well so far, but I remember
> >> reading in the past that the optimizer can sometimes
> break
> >> this approach to creating strings.
> >>
> >> If all column names are not null and the total len
> (string)
> >> does not exceed varchar(8000), is this construct safe?
> If
> >> not, why?
> >>
> >> TIA, -- Brian
> >>
> >> declare @.FldStr varchar(8000)
> >> select @.FldStr = ''
> >>
> >> select @.FldStr = @.FldStr + COLUMN_NAME
> >> from INFORMATION_SCHEMA.COLUMNS
> >> where TABLE_NAME = @.TableName
> >> order by ORDINAL_POSITION
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
the optimizer behavior luckily generated a plan for that concatenated the
values in a way you intended. However, this is a risky proposition, since
this is undocumented, inconsistent and thus unreliable. Here are some trials
that can break your luck.
--#1 ( Add a TOP clause)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT TOP 100 PERCENT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#2 ( Add a DISTINCT)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT DISTINCT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#3 ( Add a CROSS JOIN)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + c1.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c1, (SELECT 1) D (n)
WHERE TABLE_NAME = 'test'
ORDER BY ORDINAL_POSITION;
SELECT @.FldStr;
--#4 (Use another view)
DECLARE @.FldStr VARCHAR(8000)
SET @.FldStr = ''
SELECT @.FldStr = @.FldStr + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE TABLE_NAME = 'test'
ORDER BY TABLE_NAME;
SELECT @.FldStr;
--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" wrote
> In this case, it seems to work because for INFORMATION_SCHEMA.COLUMNS view
> the optimizer behavior luckily generated a plan for that concatenated the
> values in a way you intended..
Where I live everyone drives a SUV.I drive a Benz 560 SL.
They keep telling me 'this is a risky proposition, since
it's undocumented, inconsistent and thus unreliable' :~)

Wednesday, March 28, 2012

Is this statement vulnerable to code injection?

I found a bit of code my comapny got froma third party group that I
thought was vulnerable to code injection but I can't prove it since I
can't figure out a way to hack it. I can point the boss to sources that
say this type of programming is a Bad Thing (TM) but they want more.
Can someone help me?
The code is in asp and looks something like the following:
sLookupText = <users free text input>
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
sLookupText = Trim(sLookupText)
...
sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
Have com object execute sql.
The tricky part is that the dynamic sql is executed by the same third
party venders com objects so I don't see what happens to the query
between the time the asp hands it off and it is executed.
This is the error I get from the com obvject when I run it with the
input 1/'';select * from x;
Zero records match the criteria '1/'';select * from x;'
If I take the exact same query and run it in Query Analyzer I get the
message
Unclosed quotation mark before the character string ...
Which says I'm wrong when I think \' escapes the ' mark. So now I'm
wondering if maybe the code IS safe since the com object is sending a
different message and all the quotes are doubled in the message and all
the % are stripped.
Do the two lines
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
make it safe?
Thoughts?Michael, the only way to know what the com object sent to SQL Server is usin
g
SQL Profiler. I think the error show the com object is replacing de ' with
'', but using SQL Profiler is the easer way to know.|||sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
sLookupText = "1;-- ALTER DATABASE MSDB SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;GO;DROP DATABASE MSDB;--"
Upps... That can be something very wacky. Dynamic SQL is the hell for
security, so better have a chat with the vendor :-)
HTH, Jens Suessmeyer.|||I, uh, don't think I want to test that particular example! that drop
database part looks scary, though I'm guessing that rollback immediate
does something to undo it?
Anyway, from what I can tell, with your intput the query will be
coverted into.
select <parameter list> from table where <some field> like '1;-- ALTER
DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO;DROP DATABASE
MSDB;--%' order by <order clause>
As far as I can tell you never escaped out of the single quotes! Why do
you think any sql is injected?|||Michael wrote:
> I found a bit of code my comapny got froma third party group that I
> thought was vulnerable to code injection but I can't prove it since I
> can't figure out a way to hack it. I can point the boss to sources
> that say this type of programming is a Bad Thing (TM) but they want
> more. Can someone help me?
> The code is in asp and looks something like the following:
> sLookupText = <users free text input>
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> sLookupText = Trim(sLookupText)
> ...
> sSQL = "select <parameter list> from table where <some field> like " +
> sLookupText + "% order by <order clause>"
> Have com object execute sql.
> The tricky part is that the dynamic sql is executed by the same third
> party venders com objects so I don't see what happens to the query
> between the time the asp hands it off and it is executed.
> This is the error I get from the com obvject when I run it with the
> input 1/'';select * from x;
> Zero records match the criteria '1/'';select * from x;'
> If I take the exact same query and run it in Query Analyzer I get the
> message
> Unclosed quotation mark before the character string ...
> Which says I'm wrong when I think ' escapes the ' mark. So now I'm
> wondering if maybe the code IS safe since the com object is sending a
> different message and all the quotes are doubled in the message and
> all the % are stripped.
> Do the two lines
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> make it safe?
>
They make it safe when the hacker uses ' to close the quote. However, what
if he gets clever and uses the char function? OK you can do some
validation/replacing on that. But then, what if he switches to one of the
other techniques to be found in these articles:
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
The safest way to run the above query is by using parameters. There is no
way to inject sql if data is being passed via parameters.
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Llike Bob already said, he could use the CHAR function.
BTW, Rollback won=B4t do any undo of the ALTER DATABASE rather than
rolling back all transaction which are currently running.
HTH, jens Suessmeyer.|||Well, I found another section of the web site with a less protected
query. I was able to convert the query (where the stuff in <> is either
table specific information not relevant to the question or user
input)...
select <columnlist> from <table> where <column> like '<users input>%'
order by <columnlist>
to
select <columnlist> from <table> where <column> like '%'; update
<table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
by <columnlist>
by entering
%'; update <table2> set <column> = 1 where <pk column> = <pk
value>;commit;--
as the input. Nice, huh! But when I checked the database nothing
happened. When I went to the DBA he used Embarcadero DBArtisan
(whatever that is) to verify that the test was sent as expected. It
was, but no injection attack worked! Any idea why? Does sqlserver allow
multiple commands on a single line? I seem to recall on my Internet
travels that one guru said that it didn't.
I really don't like seeing a dynamic query being constructed like this
but I can't seem to prove it is a problem :( I feel it is WRONG in my
bones, but I need proof to leverage a change.|||Can anyone explain why the injection attack did not work?|||Can you post a repro, so we have something to test?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michael" <miteke@.gmail.com> wrote in message
news:1143732749.601504.212240@.e56g2000cwe.googlegroups.com...
> Can anyone explain why the injection attack did not work?
>|||On 29 Mar 2006 11:53:30 -0800, Michael wrote:

>Well, I found another section of the web site with a less protected
>query. I was able to convert the query (where the stuff in <> is either
>table specific information not relevant to the question or user
>input)...
>select <columnlist> from <table> where <column> like '<users input>%'
>order by <columnlist>
>to
>select <columnlist> from <table> where <column> like '%'; update
><table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
>by <columnlist>
>by entering
>%'; update <table2> set <column> = 1 where <pk column> = <pk
>value>;commit;--
>as the input. Nice, huh! But when I checked the database nothing
>happened. When I went to the DBA he used Embarcadero DBArtisan
>(whatever that is) to verify that the test was sent as expected. It
>was, but no injection attack worked! Any idea why? Does sqlserver allow
>multiple commands on a single line? I seem to recall on my Internet
>travels that one guru said that it didn't.
Hi Michael,
SQL Server definitely allows multiple commands on one line.
Have you checked that the command that was eventually actually sent to
the server for execution was exactly as above? I'd recommend you to
insert a PRINT statement in the application just before the SQL gets
sent to the server. Or, if you can't touch the application, set up
Profiler to run a trace. A smart parser that removes quotes, reserverd
words, punctuation marks and such would have prevented your attempt to
inject SQL.
If you have verified that this is the actual code that ran, then you can
be 100% sure that SQL Server tried to do the update. It might have
failed because the SQL is executed in the context of an account with
limited permissions (that would have resulted in an error - did you see
an error when you tested it? Maybe the app has intercepted the error
message? Again, running Profiler might reveal more info). Another reason
why this update might fail is an AFTER trigger initiating a ROLLBACK, or
an INSTEAD OF trigger simply disregarding your change; in those cases,
you don't even get an error message.
Hugo Kornelis, SQL Server MVP

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/l...an2.asp?ad_id=7
http://brettatkin.com/clients/tgc/l...ean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%> 
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%> 
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%> 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%> 
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%> 
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%> 
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%> 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%> 
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%> 
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% > 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value )%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").V alue)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%>
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%>
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)% > 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value )%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value )%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value )%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%>
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value )%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
Submit:<%=(rs_listing.Fields.Item("submit_date").V alue)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
Brett
If the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Is this some Bug - Missing data...

I just transitioned from Access to SQL Server 2005 - my first time
using SQL Server.
Please look at the following two pages. They use the same code to
retrieve a record from a View in the database.
http://brettatkin.com/clients/tgc/listings_edit_clean2.asp?ad_id=7
http://brettatkin.com/clients/tgc/listings_edit_clean.asp?ad_id=7
The first page has data missing. The second page has all the data.
The only difference is the order in which the call for the data is
made.
Here is my code (first page):
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%> 
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%> 
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%> 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%> 
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
Second Page:
<%@.LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rs_listing__MMColParam
rs_listing__MMColParam = "1"
If (Request.QueryString("ad_id") <> "") Then
rs_listing__MMColParam = Request.QueryString("ad_id")
End If
%>
<%
Dim rs_listing
Dim rs_listing_numRows
Set rs_listing = Server.CreateObject("ADODB.Recordset")
rs_listing.ActiveConnection = MM_tgc_STRING
rs_listing.Source = "SELECT * FROM dbo.qry_ad_detail_admin WHERE ad_id
= " + Replace(rs_listing__MMColParam, "'", "''") + ""
rs_listing.CursorType = 0
rs_listing.CursorLocation = 2
rs_listing.LockType = 1
rs_listing.Open()
rs_listing_numRows = 0
%>
Ad ID: <%=(rs_listing.Fields.Item("ad_id").Value)%> 
Ad Description:
<%=(rs_listing.Fields.Item("ad_description").Value)%> 
Ad Price: <%=(rs_listing.Fields.Item("ad_price").Value)%> 
Expiration:
<%=(rs_listing.Fields.Item("ad_expiration_date").Value)%> 
Username: <%=(rs_listing.Fields.Item("user_name").Value)%> 
First Name: <%=(rs_listing.Fields.Item("first_name").Value)%> 
Last Name: <%=(rs_listing.Fields.Item("last_name").Value)%> 
Main Cat:<%=(rs_listing.Fields.Item("main_cat").Value)%> 
Sub Cat:<%=(rs_listing.Fields.Item("sub_cat").Value)%> 
Title:<%=(rs_listing.Fields.Item("ad_title").Value)%> 
Price:<%=(rs_listing.Fields.Item("ad_price").Value)%> 
Payment Methods: 
Check: <%=(rs_listing.Fields.Item("pm_check").Value)%> 
Credit Card: <%=(rs_listing.Fields.Item("pm_credit_card").Value)%> 
PayPal: <%=(rs_listing.Fields.Item("pm_paypal").Value)%> 
Money Order: <%=(rs_listing.Fields.Item("pm_money_order").Value)%> 
Image 1: <%=(rs_listing.Fields.Item("ad_image1").Value)%> 
Submit:<%=(rs_listing.Fields.Item("submit_date").Value)%>
<%
rs_listing.Close()
Set rs_listing = Nothing
%>
What is going on here? It just doesn't make sense.
I would be grateful for any help.
Thanks.
BrettIf the datatype of ad_description is text/ntext, maybe this can help:
http://support.microsoft.com/default.aspx/kb/175239
Razvan

Friday, March 23, 2012

Is this piece of code dangerous?

I'm building a site, and while stress testing it I received a few exceptions when the SQL Server was under relatively high load. Originally I was opening the connection when required in a particular Sub as follows (and then closing it when I was finished with it):

If Not MyConnection.State = ConnectionState.Open Then MyConnection.Open()

The probelm however was that from time to time the connection state was Opening instead of Closed or Open. So I am considering using the following piece of code instead:

If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open

Loop
ElseIf MyConnection.State = ConnectionState.Broken Or MyConnection.State = ConnectionState.Closed Then
MyConnection.Open()
End If

I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

plenderj:
If MyConnection.State = ConnectionState.Connecting Then
Do Until MyConnection.State = ConnectionState.Open
Loop
I'm a little worried about the Do...Loop in there, but I don't see how it should be a problem. Any thoughts?

Yes, it is a problem. While you're waiting, doing nothing, your thread is chewing up CPU, which could be better used by other threads and processes, including the SQL client. A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

A better solution yet would be to wait on the StateChange event of the connection, because then you wouldn't use ANY CPU while waiting on the connection, but it would require more code, and if this doesn't happen often, wouldn't buy you much.

|||

mpswaim:
A much better solution would be to stick a Thread.Sleep(10) in your loop. It's a small enough interval that it won't hurt, but it won't peg your CPU at 100%.

DOH! How did I forget about that lol. Thanks for spotting it :D

Wednesday, March 21, 2012

Is this Code right

Hi,

This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

I am also creating a Internal parameter called Split which is an integer which has the following expression

select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.

but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.

Some one please please help me

So what can i do in order to by pass it.

Regards,

Karen

Karenros wrote:

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

The first time you run this it is creating a table called TmpResults. The second time you run this, it tries to create a table called TmpResults, but it looks in your database and finds that there is already one there, thus the error.

|||

so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.

Can u please help me out.

|||

how can i take the results of the sproc and insert it into a table? Is it possible to do it...

|||

Have you tried using functions instead?

You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.

|||

can u please give me an example of that. or do u mean write a custom code to do it?

Regards

Karen

|||

Code Snippet

USE [database]

/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[func2]

(

@.StartDate varchar(100),

@.EndDate varchar(100)

)

RETURNS TABLE

RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)

Then you could put this in a stored procedure:

Code Snippet

select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')

As you can see, a table is returned from func2 and you can select from it.

In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.

|||

Greg,

Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows

ALTER Function [dbo].[Func2]

(

@.PlanId int

)

RETURNS Table

Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)

and this is the sproc that i am using to poplulate my report it is as follows

ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)

-- History

-- 08/17/2004 svanpatter/JSWCO initial version created

-- 08/30/2004 svanpatter/JSWCO add

-- Select available funds

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

-- Fund.[FundName] AS InvestmentName,

CASE

WHEN

PlanFund.PlanFundDisplayName IS NULL

THEN

Fund.ShortName

ELSE PlanFund.PlanFundDisplayName

END InvestmentName,

'F' AS InvestmentType,

--EmpIncrementPct =

--CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

--END,

--PlanFund.PlanId As InvestmentID

PlanFund.IsPortfolioFundOnly,

PlanFund.FundDisplayOrder As InvestmentID

FROM

[ClientPlan]

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"

INNER JOIN Fund ON PlanFund.FundId = Fund.FundId

--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID

WHERE

[ClientPlan].PlanId = @.PlanId

UNION

-- Select Portfolios

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

PlanPortfolio.PortfolioName AS InvestmentName,

'P' AS InvestmentType,

--EmpIncrementPct =

-- CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

-- END,

NULL,

PlanPortfolio.PortfolioId As InvestmentID

FROM [ClientPlan]

INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

WHERE

[ClientPlan].PlanId = @.PlanId

ORDER BY

InvestmentType, InvestmentID

Select RowId from dbo.Func2(@.PlanId)

As u can see at the end of the sproc i am calling the function...

and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

any help will be appreciated

|||

Karenros wrote:

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

Are you using rpt_SelectInvestments as the sproc in your report?

If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.

|||

ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

Regards

Karen

|||

Karenros wrote:

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

For the first time when you call your procedure from report with all valid parameter value. It will create list of all parameter for your report. which are useed to call the procedure next time. And you can modify parameter from menu Report - > Report parameter...

If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.

|||

Hi its Me,

Thanks for your answer..

So in my sproc if i do

Create proc [dbo].[procname]

@.PlanId as integer,

@.Count int output

Select

<whatever> i want

fromm

tablename

Union

Select statment

where PlanId = @.PlanId

and then at the end i am setting

SEt @.Count = @.@.RowCount

Return @.count.

When i run the sproc it asks me a value for @.Count...

What should i do..

Regards

Karen

|||

select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..

|||

thanks for ur answer... But how can i get value of the output parameter in the report?

Regards,

Karen

|||

In expression just write :

=Parameters!Count.Value

And you will get the value of the parameter..

Is this Code right

Hi,

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

I am also creating a Internal parameter called Split which is an integer which has the following expression

select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.

but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.

Some one please please help me

So what can i do in order to by pass it.

Regards,

Karen

Karenros wrote:

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

|||

so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.

Can u please help me out.

|||

how can i take the results of the sproc and insert it into a table? Is it possible to do it...

|||

Have you tried using functions instead?

You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.

|||

can u please give me an example of that. or do u mean write a custom code to do it?

Regards

Karen

|||

Code Snippet

USE [database]

/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[func2]

(

@.StartDate varchar(100),

@.EndDate varchar(100)

)

RETURNS TABLE

RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)

Then you could put this in a stored procedure:

Code Snippet

select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')

As you can see, a table is returned from func2 and you can select from it.

In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.

|||

Greg,

Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows

ALTER Function [dbo].[Func2]

(

@.PlanId int

)

RETURNS Table

Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)

and this is the sproc that i am using to poplulate my report it is as follows

ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)

-- History

-- 08/17/2004 svanpatter/JSWCO initial version created

-- 08/30/2004 svanpatter/JSWCO add

-- Select available funds

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

-- Fund.[FundName] AS InvestmentName,

CASE

WHEN

PlanFund.PlanFundDisplayName IS NULL

THEN

Fund.ShortName

ELSE PlanFund.PlanFundDisplayName

END InvestmentName,

'F' AS InvestmentType,

--EmpIncrementPct =

--CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

--END,

--PlanFund.PlanId As InvestmentID

PlanFund.IsPortfolioFundOnly,

PlanFund.FundDisplayOrder As InvestmentID

FROM

[ClientPlan]

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"

INNER JOIN Fund ON PlanFund.FundId = Fund.FundId

--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID

WHERE

[ClientPlan].PlanId = @.PlanId

UNION

-- Select Portfolios

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

PlanPortfolio.PortfolioName AS InvestmentName,

'P' AS InvestmentType,

--EmpIncrementPct =

-- CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

-- END,

NULL,

PlanPortfolio.PortfolioId As InvestmentID

FROM [ClientPlan]

INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

WHERE

[ClientPlan].PlanId = @.PlanId

ORDER BY

InvestmentType, InvestmentID

Select RowId from dbo.Func2(@.PlanId)

As u can see at the end of the sproc i am calling the function...

and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

any help will be appreciated

|||

Karenros wrote:

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

Are you using rpt_SelectInvestments as the sproc in your report?

If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.

|||

ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

Regards

Karen

|||

Karenros wrote:

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.

|||

Hi its Me,

Thanks for your answer..

So in my sproc if i do

Create proc [dbo].[procname]

@.PlanId as integer,

@.Count int output

Select

<whatever> i want

fromm

tablename

Union

Select statment

where PlanId = @.PlanId

and then at the end i am setting

SEt @.Count = @.@.RowCount

Return @.count.

When i run the sproc it asks me a value for @.Count...

What should i do..

Regards

Karen

|||

select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..

|||

thanks for ur answer... But how can i get value of the output parameter in the report?

Regards,

Karen

|||

In expression just write :

=Parameters!Count.Value

And you will get the value of the parameter..

sql

Is this a valid substring code

I'am using the following code for my report :

len(replace(substring(FC.effectivitycalendar,((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.followupby))),((168-((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon))))+1),'-',' '))))+
len(replace(substring(FC.effectivitycalendar,1, ((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon))))))+
len(replace(substring(FC.effectivitycalendar,1,168)))*((datediff(ww,FI.followupby,FI.modifiedon))-1)

but when try this I get the following error message:

Msg 174, Level 15, State 1, Line 8
The substring function requires 3 argument(s).
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'datepart'.

Could someone help me on this?

Many Thanks

Avecinna

This looks like a disaster waiting to happen...

Tell us what you are attempting to accomplish. Perhaps there are other ways to get to your desired goal.

What is the starting date, and what kind of a date should you end up with?

|||

All because of the unblanced brackets.. Here I somewhat managed to correct this...

len(replace(substring(FC.effectivitycalendar,((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)),((168-((((datepart(dw,FI.followupby))-2)*24) +(datepart(hour,FI.modifiedon))))+1)),'-',' ')) +

len(replace(substring(FC.effectivitycalendar,1, ((((datepart(dw,FI.followupby))-2)*24)+(datepart(hour,FI.modifiedon)))),'-',' '))+

len(replace(substring(FC.effectivitycalendar,1,168),'-',' '))*((datediff(ww,FI.followupby,FI.modifiedon))-1)

|||

Hi everybody

Arnie...thanks for replying, I wil explain the target behind this al

I must calculate the exact overdue time of a service call........but without the time that the service company is closed.

I can infer this from a entity named effectivitycalendar: this entity exists of a long string of + and - signs

like this: --++++++-++++

This string contains exactly 168 characters that means 7 day a week.........the plus signs means that the service company is open.

So I have the deadline (followupby) and the time that the service call has been closed (modifiedon) and

There are also calls which are still open.....

some calls can be modified within a week others will take more weeks and so on...

Manivannan again many thanks....for your help!! I will trie your solution

greetings

Avecinna

|||

I suspected you were working with some form of positional manipulation. That is a 'ugly' thing to have to live with and support.

Impossible to handle any service company that opens from 8:30-5:00 -you have them either as 8-5 or 9-5, both of which are inaccurate.

A MUCH easier way to handle such problems, and much easier to manage and manipulate, is to use a 'Calendar' table. Here is an article that will give you good information about how to create and use a Calendar table.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||Yes. I agree with Arnie.|||

HI mannivannan and Arnie

I agree with you Arnie, but for now I'am close to the deadline some I finish this despite of the method I use.

I still have some problems with some code maybe you could help me on this cause I can't see what's wrong, I think it's again the unbalanced brackets...

else

len(replace(substring(fc.effectivitycalendar, ((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)),((datepart(dw,FI.modifiedon)-2)*24)+ datepart(hour,FI.modifiedon)-(((datepart(dw,FI.followupby)-2)*24)+(datepart(hour,FI.followupby)))+1)),'-',' ')) end

Many thanks

Avecinna

|||

Since it is missing parentheses, perhaps if you deconstructed the expression, and traced the left-right pairs, you would find the missing one. (By the way, that is exactly what one of us would do to find it. Imagine the satisfaction from finding it yourself...)

A nice thing about SSMS is that is helps find the right paren to a left paren. (Eventually you will discover that you have one too many right parens. Oh, but where to remove one...)

Monday, March 19, 2012

Is this a bug in IsNull?

I think I uncovered a bug in IsNull. Could someone check to see If I am
missing something here?
My view has the following code in it.
SELECT
v.QuoteId,
v.QuoteLineID,
pmc.BadLine AS BadLine1,
pic.BadLine AS BadLine2,
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
(CASE
WHEN pmc.BadLine IS NULL
THEN IsNull(pmc.BadLine, 0)
ELSE pmc.BadLine
END) +
(CASE
WHEN pic.BadLine IS NULL
THEN IsNull(pic.BadLine, 0)
ELSE pic.BadLine
END) InvalidPackage2
FROM QuoteLineValues v
LEFT JOIN PackageInvalidComponents pic
ON v.QuoteID = pic.QuoteID
AND v.QuoteLineID = pic.QuoteLineID
LEFT JOIN PackageMissingComponents pmc
ON v.QuoteID = pmc.QuoteID
AND v.QuoteLineID = pmc.QuoteLineID
When I select from this view I get
BadLine1 = NULL
BadLine2 = NULL
InvalidPackage = 2
InvalidPackage2 = 0
InvalidPackage should have the same exact value as InvalidPackage2. But it
doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
evalutate to 1 when pmc.BadLine by itself is NULL.
Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
missing something?
JeremySELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
Jens.
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>I think I uncovered a bug in IsNull. Could someone check to see If I am
> missing something here?
> My view has the following code in it.
> SELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
> FROM QuoteLineValues v
> LEFT JOIN PackageInvalidComponents pic
> ON v.QuoteID = pic.QuoteID
> AND v.QuoteLineID = pic.QuoteLineID
> LEFT JOIN PackageMissingComponents pmc
> ON v.QuoteID = pmc.QuoteID
> AND v.QuoteLineID = pmc.QuoteLineID
> When I select from this view I get
> BadLine1 = NULL
> BadLine2 = NULL
> InvalidPackage = 2
> InvalidPackage2 = 0
> InvalidPackage should have the same exact value as InvalidPackage2. But it
> doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
> evalutate to 1 when pmc.BadLine by itself is NULL.
> Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
> missing something?
> Jeremy|||Jens,
You are right in saying that there is no reason to check it again since I
already checked for null in the case statement. I was trying to exagerate th
e
absurdity of the values that IsNull returns. e.g. Inside a case statement
IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
IsNull(pic.BadLine, 0) returns 1.
Does that make any sense?
Jeremy
"Jens Sü?meyer" wrote:

> SELECT
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> Jens.
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>
>|||Try COALESCE().
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
> the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Smeyer" wrote:
>|||> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
computer?
What data type is pic.BadLine?
AMB
"Jeremy Lubich" wrote:
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Sü?meyer" wrote:
>|||Mike, et al.,
I tried COALESCE and got an interesting result. Here is what is returned by
QA.
pic.BadLine = NULL
IsNull(pic.BadLine, 0) = 1
COALEASCE(pic.BadLine, 0) = 0
Your workaround works. But, I am still stuck with the fact that IsNull looks
like it has a bug. I am worried about other parts of our database where we
also use IsNull.
I would think both of these values would return 0. Does it make any sense
why the IsNull and COALEASCE funtions do not return the same value when the
first argument is NULL?
If someone replies to this thread and says, "yup, that looks like a bug to
me", then I'll contact Microsoft to have it investigated. I just didn't want
to be presumptious in my hypothesis before I threw this out into the
community discussion.
Jeremy
"Michael C#" wrote:

> Try COALESCE().
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
>
>|||What data type is BadLine?
AMB
"Jeremy Lubich" wrote:
> Mike, et al.,
> I tried COALESCE and got an interesting result. Here is what is returned b
y
> QA.
> pic.BadLine = NULL
> IsNull(pic.BadLine, 0) = 1
> COALEASCE(pic.BadLine, 0) = 0
> Your workaround works. But, I am still stuck with the fact that IsNull loo
ks
> like it has a bug. I am worried about other parts of our database where we
> also use IsNull.
> I would think both of these values would return 0. Does it make any sense
> why the IsNull and COALEASCE funtions do not return the same value when th
e
> first argument is NULL?
> If someone replies to this thread and says, "yup, that looks like a bug to
> me", then I'll contact Microsoft to have it investigated. I just didn't wa
nt
> to be presumptious in my hypothesis before I threw this out into the
> community discussion.
> Jeremy
> "Michael C#" wrote:
>|||AMB,
Here is the DDL and statements you will need. I can reproduce it on any
database.
CREATE TABLE [dbo].[Packages] (
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Quantity] [int] NOT NULL ,
[Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[QuoteLines] (
[QuoteId] [int] NOT NULL ,
[QuoteLineId] [int] NOT NULL ,
[ParentId] [int] NOT NULL ,
[LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO QuoteLines
(QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
VALUES
(1234, 1, 0, 'S', 1, 'item-x')
GO
CREATE VIEW dbo.pkgComponents
AS
SELECT l1.QuoteID,
l1.QuoteLineID,
l1.StyleNumber,
l2.StyleNumber ComponentStyle,
l2.Quantity / l1.Quantity Quantity
FROM QuoteLines l1
JOIN QuoteLines l2
ON l1.QuoteID = l2.QuoteID
AND l1.QuoteLineID = l2.ParentID
GO
CREATE VIEW dbo.pkgMissingComponents
AS
--Find Quote Lines with Invalid Components
SELECT
1 BadLine,
l.QuoteID,
l.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
LEFT JOIN
pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for conditions that can make a package in config invalid:
--1. A Component has been added to the package definition, but not in Quote
Lines
WHERE lpack.ComponentStyle IS NULL
GROUP BY
l.QuoteID,
l.QuoteLineID
GO
CREATE VIEW dbo.pkgInvalidComponents
AS
--Find Quote Lines with Wrong Package Definitions
SELECT
1 BadLine,
lpack.QuoteID,
lpack.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
RIGHT JOIN pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for two conditions that can make a package in QuoteLines invalid:
--1. Quantities on a component don't match package definition
--2. A Component remianing in the QuoteLines has been removed from the
master package definition
WHERE
p.Quantity <> lpack.Quantity
OR p.ComponentStyle IS NULL
GROUP BY
lpack.QuoteID,
lpack.QuoteLineID
GO
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
Jeremy
"Alejandro Mesa" wrote:
> Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
> computer?
> What data type is pic.BadLine?
>
> AMB
> "Jeremy Lubich" wrote:
>|||Jeremy,
I do not know what it is wrong, but the problem is not ISNULL. If you cast
the values of pic.BadLine and pmc.BadLine then you will see 1 also.
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
IsNull(pmc.BadLine, 0) as colA,
pmc.BadLine,
cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
cast(pmc.BadLine as int) as [pmc_BadLine_int],
IsNull(pic.BadLine, 0),
pic.BadLine,
cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
cast(pic.BadLine as int) as [pic.BadLine_int]
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
AMB
"Jeremy Lubich" wrote:
> AMB,
> Here is the DDL and statements you will need. I can reproduce it on any
> database.
> CREATE TABLE [dbo].[Packages] (
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Quantity] [int] NOT NULL ,
> [Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[QuoteLines] (
> [QuoteId] [int] NOT NULL ,
> [QuoteLineId] [int] NOT NULL ,
> [ParentId] [int] NOT NULL ,
> [LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [int] NOT NULL ,
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QuoteLines
> (QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
> VALUES
> (1234, 1, 0, 'S', 1, 'item-x')
> GO
> CREATE VIEW dbo.pkgComponents
> AS
> SELECT l1.QuoteID,
> l1.QuoteLineID,
> l1.StyleNumber,
> l2.StyleNumber ComponentStyle,
> l2.Quantity / l1.Quantity Quantity
> FROM QuoteLines l1
> JOIN QuoteLines l2
> ON l1.QuoteID = l2.QuoteID
> AND l1.QuoteLineID = l2.ParentID
> GO
> CREATE VIEW dbo.pkgMissingComponents
> AS
> --Find Quote Lines with Invalid Components
> SELECT
> 1 BadLine,
> l.QuoteID,
> l.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> LEFT JOIN
> pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for conditions that can make a package in config invalid:
> --1. A Component has been added to the package definition, but not in Quot
e
> Lines
> WHERE lpack.ComponentStyle IS NULL
> GROUP BY
> l.QuoteID,
> l.QuoteLineID
> GO
> CREATE VIEW dbo.pkgInvalidComponents
> AS
> --Find Quote Lines with Wrong Package Definitions
> SELECT
> 1 BadLine,
> lpack.QuoteID,
> lpack.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> RIGHT JOIN pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for two conditions that can make a package in QuoteLines invalid:
> --1. Quantities on a component don't match package definition
> --2. A Component remianing in the QuoteLines has been removed from the
> master package definition
> WHERE
> p.Quantity <> lpack.Quantity
> OR p.ComponentStyle IS NULL
> GROUP BY
> lpack.QuoteID,
> lpack.QuoteLineID
> GO
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> Jeremy
> "Alejandro Mesa" wrote:
>|||AMB,
I have no experience with reporting bugs to Microsoft. It looks to me if I
try and email them this problem it will cost me $99. Do you know a way of
getting Microsofts attention without it costing $. How would you have this
investigated?
Jeremy
"Alejandro Mesa" wrote:
> Jeremy,
> I do not know what it is wrong, but the problem is not ISNULL. If you cast
> the values of pic.BadLine and pmc.BadLine then you will see 1 also.
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
> IsNull(pmc.BadLine, 0) as colA,
> pmc.BadLine,
> cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
> cast(pmc.BadLine as int) as [pmc_BadLine_int],
> IsNull(pic.BadLine, 0),
> pic.BadLine,
> cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
> cast(pic.BadLine as int) as [pic.BadLine_int]
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> AMB
> "Jeremy Lubich" wrote:
>

Monday, March 12, 2012

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.What does this have to do with SQL Server?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.
What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>
|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Is there something wrong with my IF code...

<%
If required_equip = Desktop Then
If required_equip = Laptop Then
End If
If required_equip = Other Then
End If
Response.Redirect "nh_request.asp"
End If
%>
Just wondering if the above would work within my form to check a radio
button on submission...
I have 1 radio button, that if selected i want the person to be taken to a
2nd form after they submit the first form... if that radio button is NOT
selected.. then they submit the form and they are done...
can anyone tell me if the above would work and if so, where i should place
it within my code so it checks when the submit button is clicked.What does this have to do with SQL Server?
http://www.aspfaq.com/
(Reverse address to reply.)
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj#03$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>|||You are asking this question in the wrong forum... This should be posted in
one of the development forums.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Daniel_Cha" <dan_cha@.hotmail.com> wrote in message
news:uj%2303$0cEHA.3420@.TK2MSFTNGP12.phx.gbl...
> <%
> If required_equip = Desktop Then
> If required_equip = Laptop Then
> End If
> If required_equip = Other Then
> End If
> Response.Redirect "nh_request.asp"
> End If
> %>
> Just wondering if the above would work within my form to check a radio
> button on submission...
> I have 1 radio button, that if selected i want the person to be taken to a
> 2nd form after they submit the first form... if that radio button is NOT
> selected.. then they submit the form and they are done...
> can anyone tell me if the above would work and if so, where i should place
> it within my code so it checks when the submit button is clicked.
>
>

Friday, March 9, 2012

Is there anything similar in TSQL to oracle code "SEQUENCE.NEXTVAL

Hello,
I trying to do a SELECT that will show me the next value automatically
everytime.
for example in Oracle, I can do
Select SEQUENCE.NEXTVAL from Dual
1
Select SEQUENCE.NEXTVAL from Dual
2
Select SEQUENCE.NEXTVAL from Dual
3
with Oracle there is a function that allows me to use auto increment value
without specifying for insert into the table.
Can I do the same with a TSQL? Anything in TSQL close to this function.
Please help. Thanks again.No there isn't a function that can do this. But can you tell what is the
actual requirement, so that we can give you an alternative
--
"sqlapprentice" wrote:

> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||You can look into using IDENTITY, but it really depends on what you are
trying to accomplish.
"sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
news:C5A27B55-64ED-468F-A61B-5DC4715687BE@.microsoft.com...
> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||in SQL server, one uses the IDENTITY attribute of a column to specify
what would be similar to an Oracle Sequence.
CREATE TABLE mytable(
entryid INT IDENTITY(1,1),
entrydata VARCHAR(100)
)
Use the following to get the current value of the "Sequence"
DBCC CHECKIDENT ('owner.tablename')
in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
and also check out DBCC CHECKIDENT.|||"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1146499671.388074.220710@.i39g2000cwa.googlegroups.com...
> in SQL server, one uses the IDENTITY attribute of a column to specify
> what would be similar to an Oracle Sequence.
> CREATE TABLE mytable(
> entryid INT IDENTITY(1,1),
> entrydata VARCHAR(100)
> )
> Use the following to get the current value of the "Sequence"
> DBCC CHECKIDENT ('owner.tablename')
> in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
> and also check out DBCC CHECKIDENT.
>
besides DBCC CHECKINDENT, you can also use
select ident_current('table')
or
select max(identitycol) from table
ident_current() is the preferred method of the two.
dean

Wednesday, March 7, 2012

Is there any way to access report objects at runtime

Hi all,

In my report, I have an image object that I want it to load dynamically at runtime. How can I access it from code.

Many thanks,

Huy Le

You can reference the image as a url, or from the database (I think). I'd probably go the url route, if you can either reference the image file itself via url, or else have a .net web page that displays the image based on query string parameters.

Steve

Is there any way of keeping all of a solution in one script project?

We do not have a source code control plug in for Visual Studio 2005 and I am trying to establish an efficient way of controlling versions of a solution I have created in BIDS, which includes SSAS objects plus an SSIS package.

As there are so many individual files for the dimensions, cubes, database, etc., it does not seem feasible to have them all checked in to our source control application and I was thinking of creating an Analysis Services Scripts project via SQL Server Mgt Studio where I could simply script out the SSAS database and the package and use these via the Deployment Wizard to deploy.

However -

a) according to BOL, an Analysis Services Scripts project has Connections, Scripts and Miscellaneous folders - when I create one, it has Queries instead of Scripts and when I script out the database I cannot see the xmla file in the project. Am I getting hold of the wrong end of the stick here?!

b) An SSIS package cannoy, I think, be scripted as such; when you deploy it creates the dtsx file and the SSIS DeploymentManifest file that installs the package, so is the best idea to keep these two files in source control?

Any advice gratefully received! Thank you

Rachel

I am a little bit surprised that your source control system has a limitation on number of files that affects you. In typical project you might have couple of dozen files, let's say up to a hundrend in a big solution - but modern source control systems can handle hundrends of thousands of files. Is there something here that I miss ?|||

In Visual Source Safe, part of Visual Studio, you do not check in and out individual files, only objects like dimensions and cubes.

Are you trying to build your own version control system? Visual Source Safe should be enough for projects with 1-5 developers?

Regards

Thomas Ivarsson

|||

Sorry, I was not making myself clear; we use MKS Source Integrity (a rather old version that does not integrate with VS) and there is no limit to the number of files it can deal with, but when I started checking in all the dim, cube, dsv, database, ds, dwproj, sln files etc it became apparent that it would be difficult to manage in that you would need to know which files would be affected by a change before you made it - I ended up checking everything out every time (a waste of time and effort!).

So I thought one script file for the database plus whatever the relevant files for the SSIS package are might be a better way forward, but would appreciate advice.

|||If only we had VSS! (see my response to Mosha above)|||So...any further advice from anyone?

is there any way AMO app can get information from data source without reprompting user for user

I'm writing some code that will use AMO to access an AnalysisServices cube. I've found that some information that I need doesn't appear to be available from the AMO interfaces directly, so I need to get the information from one of the data sources. I tried using ADO.NET to connect to the data source, but I found that the Microsoft.AnalysisServices.DataSource.ConnectionString has the authentication information (i.e. username and password) removed. So... is there any other alternative that will allow me to get information from the data source (i.e. issue SQL command to the data source and retrieve the result), that does not require me to prompt the user for the authentication information? Can I somehow go through the AMO and have it issue the command and get the result? I'm new to this and hoping that someone can provide a suggestion.

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Have you had a look at some of the schema rowsets that are available? If you are after lists of members or something like that there may be another way of achieving what you are after. Someone might be able to help if you could explain what information you are after.

is there any way AMO app can get information from data source without reprompting user for u

Thanks in advance!

Arden

I don't think there is anyway of doing this.

Friday, March 30, 2012

Is this T-SQL dangerous/not reliable

Wednesday, March 28, 2012

Is this statement vulnerable to code injection?

Is this some Bug - Missing data...

Is this some Bug - Missing data...

Is this some Bug - Missing data...

Friday, March 23, 2012

Is this piece of code dangerous?

Wednesday, March 21, 2012

Is this Code right

Is this Code right

Is this a valid substring code

Monday, March 19, 2012

Is this a bug in IsNull?

Monday, March 12, 2012

Is there something wrong with my IF code...

Is there something wrong with my IF code...

Is there something wrong with my IF code...

Friday, March 9, 2012

Is there anything similar in TSQL to oracle code "SEQUENCE.NEXTVAL

Wednesday, March 7, 2012

Is there any way to access report objects at runtime

Is there any way of keeping all of a solution in one script project?

is there any way AMO app can get information from data source without reprompting user for user

is there any way AMO app can get information from data source without reprompting user for u

better method for DTS

Blog Archive

About Me