Get Array value from a loop?

Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??

=====================================

DECLARE @.ABBR AS NVARCHAR(50)
SET @.ABBR = 'CHP,CNH,COW'

DECLARE @.SEP AS NVARCHAR(5)
SET @.SEP = ','

WHILE patindex('%,' + @.ABBR + ',%', @.ABBR ) > 0
BEGIN

-- do the rest

END

I used a stored procedure as this

Create proceduresp_ParseArray( @.Arrayvarchar(1000),@.separatorchar(1) )ASset nocount on-- @.Array is the array we wish to parse-- @.Separator is the separator charactor such as a commadeclare @.separator_positionint-- This is used to locate each separator characterdeclare @.array_valuevarchar(1000)-- this holds each array value as it is returned-- For my loop to work I need an extra separator at the end. I always look to the-- left of the separator character for each array valueset @.array = @.array + @.separator-- Loop through the string searching for separtor characterswhile patindex('%' + @.separator +'%' , @.array) <> 0begin-- patindex matches the a pattern against a stringselect @.separator_position = patindex('%' + @.separator +'%' , @.array)select @.array_value =left(@.array, @.separator_position - 1)-- This is where you process the values passed. -- Replace this select statement with your processing -- @.array_value holds the value of this element of the arrayselect Array_Value = @.array_value-- This replaces what we just processed with and empty stringselect @.array = stuff(@.array, 1, @.separator_position,'')endset nocount offgo

Hope this helpsql

Get a value from SQL Database...

Sorry, this maybe a very easy problem... I just want to know how get a value from SQL database ... since I am new to SQL server...

I already have code like this:

Dim queryString As String = "SELECT Password FROM [User] WHERE Name = 'Sprite'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

What should I do next to get the value ?

thx for your help

:)

Just had a try, and it works~ haha...

Here is the code for other newbies...

Dim connectionString As String = "Connection String Here"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "Your SQL String Here"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbPassword as String
dbConnection.Open()
dbPassword = CType(dbCommand.ExecuteScalar, String)
dbConnection.Close()

Sorry for the trouble I made...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Get a value

This is my code...

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo WHEREusername=@.UserName AND

password=@.Password"

myConnection = New SqlConnection("server=

(local);database=Database;trusted_connection=true")

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

On the table 'tblLoginInfo' there are 3 fields... username, password and destination.
When a user clicks on the btnLogin... i want to get the value of the destination field that

lines up with the same username and password fields and place it in a string called 'test'.

Cannning

Hi, just replace count(*) with destination:

strSQL = "SELECT destination FROM tblLoginInfo WHEREusername=@.UserName AND

password=@.Password"

myConnection = New SqlConnection("server=

(local);database=Database;trusted_connection=true")

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()

string test = (string) myCommand.ExecuteScalar ( ) ;

myConnection.Close()

Hope this helps

Get A return value with EXEC?

Hi, I have an sql query like this :

DECLARE
@.TableName varchar(200),
@.ColumnName varchar(200),
@.EmployeeID varchar(200),
@.Result varchar(200);

SET @.TableName = 'Customer';
SET @.ColumnName = 'First_Name';
SET @.CustomerID = 28;

-- This line return Error
SET @.Result = EXEC ('select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Print @.Result;

I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC?

Thanks

I think you can do the following:
EXEC ('SET @.Result = select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Get a return value when calling a SP from within ASP

I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
<HTML>
<HEAD><TITLE>test</TITLE></HEAD>
<BODY>
<%
Set ADODBCon = Server.CreateObject("ADODB.Connection")
Dim StrConn, rs, MyInput, MyResult
MyInput = 123
ADODBCon.Provider = "SQLOLEDB"
strConn = "Data Source=MySQLServer;"
strConn = strConn & "Initial Catalog=MyDatabase;"
strConn = strConn & "User Id=JohnD;"
strConn = strConn & "Password=MyPwd;"
ADODBCon.Connectionstring = strConn
ADODBCon.open
sSql = "usp_test " & MyInput
Set rs = ADODBCon.Execute(sSql, out MyResult)
%>
</BODY>
</HTML>"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.co...y/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:

> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> <HTML>
> <HEAD><TITLE>test</TITLE></HEAD>
> <BODY>
> <%
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
> </BODY>
> </HTML>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

Get a return value when calling a SP from within ASP

I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
&

---

test"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.com/en-us/library/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> &

---

> test
>
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
>
>

---

>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

get a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

Get a max value from one table base on date from another.

I have a problem where I need to join multiple tables. Part of the query needs me to make the following relationship in a join.

Say I have a table with items, prices, and dates.

PriceTable

ITEM PRICE DATE

A 1.00 5/5/2000

B 3.00 1/1/2000

A 2.50 6/5/2004

....

This table represents an items price from the date on. So Item 'A' costed 1.00 from 5/5/2000 through 6/5/2004, then it costed 2.50 from that day on.

Now say I have a table of transactions with items, amount bought, and dates.

TransactionTable

ITEM AMOUNT DATE

A 5 6/6/2003

A 1 8/5/2003

A 2 8/5/2004

The total for A should come out to be 11.00. There are multiple Items and multiple price changes.

If someone could point out how a inner join of this nature would work it will help me in my query. I guess the reason i say inner join is because I am joining multiple other tables to do my query.

My current Price Table has a start date and an end date and my query looks something like this. "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable Inner Join TransactionTable ON TransactionTable.Date Between PriceTable.StartDate and PriceTable.EndDate AND PriceTable.Item = TransactionTable.Item".

I want something like this because the tables need to be in the format above "Select SUM(PriceTable.Price * TransactionTable.Amount) From PriceTable INNER JOIN TransactionTable ON Max(PriceTable.Date) WHERE PriceTable.Date <= TransactionTable.Date AND PriceTable.Item = TransactionTable.Item".

Hope that made sense, thanks.

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date)

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

|||

I had to change it slightly:

SELECT c.item, SUM(c.ItemAmount) AS "Total" FROM (SELECT a.item , a.amount*(SELECT TOP (1) b.price

FROM PriceTable AS b

WHERE (b.Date <= a.Date) AND b.item = a.item

ORDER BY b.Date DESC) AS "ItemAmount"

FROM TransactionTable AS a) AS c

GROUP BY c.item

Thank you for your help. Now I just have to integrate this into my larger query.

sql

Get 1st Value in Group

I have a grouping view and I want to be able to get the 1st value in a
column. In Access I would use First(colname) but I couldn't fing anything
similar in BOL. Thanks.
DavidIn SQL Server there is no direct equivalent of the Access FIRST and
LAST
functions. A table in SQL is not logically ordered so there is no first
or
last row - you have to specify the column/expression that determines
which
rows you want.
Here's an example taken from the Pubs database giving the first and
last
title based on the MIN and MAX values of Title_id.
SELECT
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MIN(title_id)
FROM Titles)) AS First_Title,
(SELECT title
FROM Titles
WHERE title_id
= (SELECT MAX(title_id)
FROM Titles)) AS Last_Title
A common requirement is to extract a single row of a group based on
some
criteria. The query below retrieves values from the "Last" (maximum
title_id) row for each Type in the Titles table.
SELECT T1.type, T1.title_id, T1.title, T1.price
FROM titles AS T1
JOIN
(SELECT MAX(title_id) AS title_id
FROM titles
GROUP BY type) AS T2
ON T1.title_id = T2.title_id
If you need to retrieve the first or last rows based on the order in
which
they were entered into a table then you need to add a DATETIME column
to the
table to record the entry date:
date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
David Portas
SQL Server MVP
--|||FIRST has no meaning in a relational database. Maybe you are looking for
MIN or MAX. Or, if you show your table structure, sample data and desired
results (see http://www.aspfaq.com/5006), someone can give you more explicit
help.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OYJV5aSGFHA.428@.TK2MSFTNGP15.phx.gbl...
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing anything
> similar in BOL. Thanks.
> David
>|||David C wrote:
> I have a grouping view and I want to be able to get the 1st value in a
> column. In Access I would use First(colname) but I couldn't fing
> anything similar in BOL. Thanks.
> David
What do you mean "First value in a column"?
A column only contains a single value. Are you dealing with some sort of
delimited set of values stored in a single column? If so, you can use
CHARINDEX() function to look for the delimiter you use to delimit the
"words" and then use SUBSTRING() to pull it out.
Probably better to do this on the client, though.
Can you post your DDL and example data.
David Gugick
Imceda Software
www.imceda.com|||This is what I am using.
SELECT TOP 100 PERCENT PersonID, MIN(DISTINCT HistoryText) AS
FirstOfHistoryText
FROM dbo.HistoryDates
WHERE (DateCode = N'H')
GROUP BY PersonID
ORDER BY MAX(HistoryDate) DESC
I thought that it would give me the HistoryText value for the PersonID
for the latest HistoryDate with DateCode = 'H' for that PersonID.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Try:
SELECT personid, historytext
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
However, if you have duplicate values in the HistoryDate column that
might return more than one row for a given PersonId. Maybe that's not
what you want, in which case you might do:
SELECT personid, MIN(historytext)
FROM dbo.HistoryDates AS H
WHERE datecode = N'H'
AND historydate =
(SELECT MAX(historydate)
FROM dbo.HistoryDates
WHERE personid = H.personid)
GROUP BY personid
Hopefully you can see now that this won't return the "first" or "last"
of anything - tables have no fixed concept of order. These queries
return the row(s) for the maximum date. In the case of the second
query, you'll get, at most, one row per PersonId with the
MIN(historytext) value.
David Portas
SQL Server MVP
--|||Yes, there will be multiples for any PersonID. I want to get the text
and date from the MAX(HistoryDate) for each PersonID. I will try your
last suggestion.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||David,
I think you have the N'H' conditions in the wrong place.
Your query might return nothing for some personid, if
for the latest historydate it is not the case that datecode = N'H',
even if that person does have some rows where datecode = N'H'
I think (but am not sure) that David wants exactly one
result row for every personid with any N'H' rows, and the
one he wants is the latest of those N'H' rows.
If (personid, historydate) is unique, you can just move the
(datecode = N'H') condition into the subquery. If only
(personid, historydate, datecode) is unique, then it should
be in both the main query and the subquery.
Steve Kass
Drew University
David Portas wrote:

>Try:
>SELECT personid, historytext
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
>However, if you have duplicate values in the HistoryDate column that
>might return more than one row for a given PersonId. Maybe that's not
>what you want, in which case you might do:
>SELECT personid, MIN(historytext)
> FROM dbo.HistoryDates AS H
> WHERE datecode = N'H'
> AND historydate =
> (SELECT MAX(historydate)
> FROM dbo.HistoryDates
> WHERE personid = H.personid)
> GROUP BY personid
>Hopefully you can see now that this won't return the "first" or "last"
>of anything - tables have no fixed concept of order. These queries
>return the row(s) for the maximum date. In the case of the second
>query, you'll get, at most, one row per PersonId with the
>MIN(historytext) value.
>
>

Get @@IDENTITY of new inserted record,

I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000

Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.

My code so far that works... but Where do I put @.@.IDENTITY ?
How do I call or assign the @.@.IDENTITY value to a value in my aspx.vb code page?

Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
-------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla......)
VALUES (@.AppName, @.Acronym, @.Description, bla bla bla bla......)

----------

Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@.app_id").Value = Session("App_ID")
.Parameters("@.AppName").Value = txtAppName.Text
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........

End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''Session("App_ID") = whatever the @.@.IDENTITY is...'''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception

End Try
End Sub

Anyone have the lines of code that does this?

Any advise or examples :) thanks you.Well you have a few options here. to name a couple, place it in an output parameter or a return value. I think the better choice is an output param as return is typically reserved for returning custom errors and messages.

.NET Data Access Architecture Guide

Also have a look at scope_identity vs @.@.identity

get % distribution of invalid data?

We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)

My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.

col1 col2 col3 col4

1 1 1 1

1 -1 -1 1

-1 1 -1 1

-1 1 -1 1

I would want my % distribution query/ssis result to return:

col1 col2 col3 col4

.5 .25 .75 0

I do not want to do a select/ssis process for each column. Any way to do this all in one shot?

You could write a script component to track the number of good and bad values, and output them to an asynchronous output. Or execute a SQL statement after the data flow to get the statistics.|||

jwelch wrote:

execute a SQL statement after the data flow to get the statistics.

That's what I would try first....just to keep it simpler

generating sql string for execution

I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

To generate this: 'abcd' like 'a%'
& verify if the condition is satisfied I've done the following:

CREATE TABLE #Temp(Result varchar(10))
declare @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50),
@.expr as varchar(50),@.result as varchar(10)
set @.cond1 = '''abcd'''
set @.op = 'like'
set @.cond2 = '''a%'''
set @.expr = @.cond1 + ' ' + @.op + ' ' + @.cond2
insert into #temp exec ('select case when ' + @.expr + ' then ''true'' else ''false'' end')
if exists (select result from #temp where result = 'true')
select 'it is true'
else
select 'it is false'
drop table #temp

It works, but a bit clumsy. Is there a more elegant way to do this ?How about something mildly perverse like:DECLARE @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

EXECUTE ('SELECT ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END')The extra parentheses are just digital "seat belts" in case anything goes wrong cooking up your expression.

-PatP|||Thanks Pat, very creative.

An extension on that: how do I get the result of the execute into a variable ?

with a select one can do this:

declare @.temp as varchar(10)
select @.temp = (select 'abc')
print @.temp

but that doesn't work with an exec

Thanks
Colin|||Now we need to get "creative" to make that happen!DECLARE
@.cond1 AS VARCHAR(50)
, @.cond2 AS VARCHAR(50)
, @.cmd AS NVARCHAR(200)
, @.op AS VARCHAR(4)
, @.result AS NVARCHAR(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

SET @.cmd = 'SELECT @.i = ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END'

EXECUTE sp_executesql @.cmd, N'@.i NVARCHAR(50) OUTPUT ', @.result OUTPUT

SELECT @.result -- Just to show it worked-PatP

Generating Percent problem with divide by zero

Im trying to generate a percentage value on a agregated field
but in some groups there isn't any information so it tries to divide by zero
and i get an error
So i thought id use a IIf to se if either the Value or the Total was zero
and write out 0 % but the IIf seems to be running the calculation anyway..
heres the code that i'm using as an expression for the percentage field
=IIf(Sum(Fields!MyValue.Value) = 0 Or Sum(Fields!MyTotal.Value) = 0,
0,
SUM(Fields!MyValue.Value) / SUM(Fields!MyTotal.Value)
)
Is this a bug, or is their another way to se that reporting services doesn't
divide by zero?
// LeonHi Leon,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to aviod
"divide by zero" error by using IIF. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, IIF is a function call and will therefore evaluate
all arguments so it will result in a Division by Zero exception. You may
modify your expression below
=IIf(Fields!MyValue.Value = 0, 0, Fields!MyValue) /
IIf(Fields!MyTotal.Value = 0, 1, Fields!MyTotal.Value)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Generating hash value

Following Microsoft recommendations, I'd like to store a one-way passport
hash of a user's password. .NET provides method
FormsAuthentication.HashPasswordForStoringinConfigFile (...) to generate a
hash value with either SHA1 or MD5 algorithm. My problem is that the
password is to be generated on a workstation with no .NET installed. How can
I generate a hash value without .NET in the same way as
HashPasswordForStoringinConfigFile does? Is there any sequence of Windows
Crypto API calls with the same effect? An external stored procedure on the
server side?
EliyahuYes, CryptoAPI supports calculating hashes using functions:
CryptCreateHash
CryptHashData
CryptGetHashParam (with dwParam = HP_HASHVAL to get actual hash buffer)
Start here:
http://msdn.microsoft.com/library/d...data_hashes.asp
The byte order in the capi buffer returned is identical to data in .NET
HashPasswordForStoringinConfigFile string.
You only need to convert the byte buffer into an ordered hex-string to match
the .NET hash string.
- Michel Gallant
MVP Security
"Eliyahu Goldin" <removemeegoldin@.monarchmed.com> wrote in message
news:Orw0CVVzDHA.2932@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Following Microsoft recommendations, I'd like to store a one-way passport
> hash of a user's password. .NET provides method
> FormsAuthentication.HashPasswordForStoringinConfigFile (...) to generate a
> hash value with either SHA1 or MD5 algorithm. My problem is that the
> password is to be generated on a workstation with no .NET installed. How c
an
> I generate a hash value without .NET in the same way as
> HashPasswordForStoringinConfigFile does? Is there any sequence of Windows
> Crypto API calls with the same effect? An external stored procedure on the
> server side?
> Eliyahu
>

---

|||> Crypto API calls with the same effect? An external stored procedure on the

quote:

---

> server side?

---

you can use XP_CRYPT (www.activecrypt.com). Free version supports SHA1, MD5
and DES hashes without limitations.|||Thanks Michel and Andy,
Your answers are exactly what I need.
Eliyahu

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

Generating a value list using PATH mode

I am attempting to get data using the "for xml path" and would like to list my data in child elements. I can render the data in the format that I would want but instead of creating child elements I am left with HTML like character codes around my element name (&lt and &gt).

I am following the example outlined in http://msdn2.microsoft.com/en-us/library/ms189885.aspx section C. The example unfortunately even shows the &lt and &gt symbols. I am missing something obvious here? Any help would be greatly appreciated.

Are you using a nested query? If so add the TYPE directive to the FOR XML e.g.

Code Snippet

SELECT ProductModelID as "@.ProductModelID",

Name as "@.ProductModelName",

(SELECT ProductID as "data()"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH ('')

) as "@.ProductIDs",

(

SELECT Name as "ProductName"

FROM Production.Product

WHERE Production.Product.ProductModelID =

Production.ProductModel.ProductModelID

FOR XML PATH (''), TYPE

) as "ProductNames"

FROM Production.ProductModel

WHERE ProductModelID= 7 or ProductModelID=9

FOR XML PATH('ProductModelData')

that way the XML is not escaped.

Generate Unique Alpha numeric Value

Hi all
Can any one send me the procedure which can give me
unique alpha numeric value , that should not repeat..
thanksUse the NEWID() function. This will generate a GUID which can be implicitly converted to and from a CHAR(36) or NCHAR(36) data type.

blindman

Generate Random Numbers

Hi, how do I use the Rand Function to generate a Random integer value between 1 and 20. Any help will be appreciated.

Thanks

What about this one:

SELECT

CEILING(RAND()*20) AS myrandIntegerBetween0And20